Shipment Example using SUMX

Posted On 2021-07-21

I recently set up a Github repository for my digital Give aways and I figured this would also be a good place to store a sample file for a DAX formula I recently was using in a customer report and then a colleague said “Hey this would be great to save so that we can copy the formula in the future”. If we have use for it internally, the internet probably do to so here’s how it works.

I created some sample data for orders with a shipment date and a date for when the order was received. I wanted to calculate how much value was in the process of being shipped over time. This could have been done using a calculated column in a calendar table by simply adding the value per day. Problem is, you might want to filter it on type or something else and that wouldn’t be dynamic with a static column. The solution is spelled Sumx. the formulas with an X at the end makes calculations row by row which means you can make use of the a cell value on the specific row from one of the columns!

This is what the sample looks like visualised:

To make this, I created a table for each day like this:

Then I created a daily value column for each row:

Daily Shipping value = 
Var ShipmentDays = DATEDIFF(Factstable[Shipment Date],Factstable[Receiving Date],DAY)+1
Return
Factstable[Value] / ShipmentDays

This gives us the basic pieces we need. In the calendar table, Create a measure that looks like this that will do the actual calculations:

Value in shipment = 
// The SumX will go through every row in the Calendar Table and make the calculations row by row

SUMX('Calendar',
    CALCULATE( //Inside the SUMX, I'm using a Calculate to be able to filter the results
        SUM(Factstable[Daily Shipping value]), // I've created a custom column with daily value that we're summaring here
        FILTER(Factstable, // When suming up the daily value, we only want to include specific rows so we'll filter the fact table
            AND(Factstable[Shipment Date]<='Calendar'[Date],Factstable[Receiving Date]>='Calendar'[Date]) // The Fact table is filtered to only include rows where the shipment date is in the past or today but the receiving date is in the future or torday. This way we only get those rows where our order is currently in shipment (and we include the day that we receive the order as well)
        )
    )
)

The great thing with making this a measure is that it’s now fully dynamic, as all things should be! Using this measure as our value field, we can filter our factstable and that will effect our visual report like this:

Do you want to try it our or prefer to copy DAX code from an PBIX-file rather than from this blog, download it here from Github!

And as always, if you have any questions feel free to reach out!

Cheers!