Forum Discussion
calculation versus previous week
Hello Community,
Hope you are well.
I would appreciate it if you could help me with the below.
Table details:
The table shows the despatch schedule (in pallet quantities) of three sites. It includes dimensions such as product number, market, despatch date and transportation mode. Finally, it also includes the dates at which the extractions took place.
Outcome:
I need to calculate the differences of despatched pallets on a weekly basis , based on the two data extractions
I need to be able to filter on site (From_Location) and plant (To_Location).
Thank you in advance,
George
| Extraction_Date | Product_Number | From_Location | To_Location | Order_Number | Pallets_Number | Shipment_Date | Transport_Mode |
| 01-Jan-21 | Prod001 | Site01 | Plant01 | Order001 | 150 | 05-Jan-21 | Sea |
| 01-Jan-21 | Prod001 | Site01 | Plant01 | Order001 | 200 | 10-Jan-21 | Sea |
| 01-Jan-21 | Prod002 | Site01 | Plant02 | Order002 | 50 | 05-Feb-21 | Truck |
| 01-Jan-21 | Prod002 | Site01 | Plant02 | Order002 | 100 | 15-Feb-21 | Truck |
| 01-Jan-21 | Prod003 | Site02 | Plant02 | Order002 | 75 | 20-Feb-21 | Truck |
| 01-Jan-21 | Prod003 | Site02 | Plant02 | Order002 | 250 | 25-Feb-21 | Truck |
| 01-Jan-21 | Prod004 | Site03 | Plant02 | Order003 | 150 | 05-Mar-21 | Truck |
| 01-Jan-21 | Prod004 | Site03 | Plant02 | Order003 | 50 | 15-Mar-21 | Truck |
| 01-Jan-21 | Prod005 | Site03 | Plant03 | Order004 | 75 | 10-Apr-21 | Air |
| 01-Jan-21 | Prod005 | Site03 | Plant03 | Order004 | 25 | 20-Apr-21 | Air |
| 01-Feb-21 | Prod001 | Site01 | Plant01 | Order001 | 200 | 10-Feb-21 | Sea |
| 01-Feb-21 | Prod002 | Site01 | Plant02 | Order002 | 75 | 15-Feb-21 | Truck |
| 01-Feb-21 | Prod003 | Site02 | Plant02 | Order002 | 60 | 20-Feb-21 | Truck |
| 01-Feb-21 | Prod003 | Site02 | Plant02 | Order002 | 250 | 26-Feb-21 | Truck |
| 01-Feb-21 | Prod004 | Site03 | Plant02 | Order003 | 125 | 05-Mar-21 | Truck |
| 01-Feb-21 | Prod004 | Site03 | Plant02 | Order003 | 75 | 20-Mar-21 | Truck |
| 01-Feb-21 | Prod005 | Site03 | Plant03 | Order004 | 50 | 15-Apr-21 | Air |
| 01-Feb-21 | Prod005 | Site03 | Plant03 | Order004 | 50 | 10-Apr-21 | Air |
| 01-Feb-21 | Prod006 | Site01 | Plant04 | Order005 | 150 | 25-Apr-21 | Truck |
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
4 Replies
"I need to calculate the differences of despatched pallets on a weekly basis , based on the two data extractions"
What will this look like? Please show an example
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
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
I built the relationship between the Details table (fact table) and a Date table.
The first table was succesfully created as suggested.
PalletsQty:=sum(Details[Pallets_Number])
Row Labels 2021.1 2021.12 2021.15 2021.16 2021.17 2021.18 2021.2 2021.3 2021.6 2021.7 2021.8 2021.9 Grand Total 2021.1 150 50 75 25 150 200 50 175 250 1125 2021.6 125 75 50 50 150 200 135 250 1035 Grand Total 275 125 125 50 25 150 150 200 50 200 310 500 2160 The second table was succesfully created only when I wrote the below measure.
DeltaQty:=[PalletsQty] - CALCULATE ([PalletsQty], Details[Extraction_Year&Num] = "2021.1")
Row Labels 2021.1 2021.12 2021.15 2021.16 2021.18 2021.7 2021.8 2021.9 Grand Total 2021.6 -25 25 -25 50 150 200 -40 0 -90 Grand Total -25 25 -25 50 150 200 -40 0 -90 Regarding the MeasureX - CALCULATE(MeasureX, PREVIOUSMONTH(ExtractionDate)) that you suggested, can you please provide more input on how it can work?
For example, I can create an inactive relationship between the dates table and the details table-extraction date. But how exactly should the above measure be written? Should I use the USERELATIONSHIP function?
More to that, there is always the scenario where the extracted details might happen twice during the same month. In this case, how should your recommended measure be written (PREVIOUS MONTH would not work in this case)?
Thank you
George