Forum Discussion
calculation versus previous week
You need a Calendar table (based on Shipment Date, it should have columns for WeekNo, Year).
Create a relationship to the main table on Shipment Date
For the first table, you should be able to create a matrix with WeekNo, Extraction Date and a measure for SUM( Pallets Number).
--
The second table requires a measure using the measure already created so something like:
MeasureX - CALCULATE(MeasureX, PREVIOUSMONTH(ExtractionDate))
it won't be exactly this, DAX time intelligence features work best with a calendar, so that would involve another calendar table for extraction dates. OR you could create the previous date by calculating it manually
Hello HotChilli ,
Thank you for getting back to me.
I want the user to select any extraction date and to be able to see the differences of the despatched pallets, shown on a weekly basis (based on shipment date), between the selected extraction date and the previous extraction date.
I assume that the first step would be to calculate the number of despatched pallets per week (based on shipment date), for each data extraction, just like the table below.
The rows of the table refer to the number of pallets for each extarction date, and the columns capture the Year and Week number of the shipment date.
| 2021 | ||||||||||||
| Extraction_Date | 2 | 3 | 6 | 7 | 8 | 9 | 10 | 12 | 15 | 16 | 17 | 18 |
| 2021.1 | 150 | 200 | 50 | 175 | 250 | 150 | 50 | 75 | 25 | |||
| 2021.6 | 200 | 135 | 250 | 125 | 75 | 50 | 50 | 150 |
The second step would be to select the latest extraction date and show the differences of the number of pallets, for each week based on shipment date.
| 2021 | ||||||||||||
| Extraction_Date | 2 | 3 | 6 | 7 | 8 | 9 | 10 | 12 | 15 | 16 | 17 | 18 |
| 2021.6 | -150 | -200 | -50 | 200 | -40 | 0 | -25 | 25 | -25 | 50 | -25 | 150 |
I would also like the user to be able to use the other dimensions (such as from location , to location , transport mode) as filters.
Please let me know if this is more clear now.
Kind regards,
George