calculation versus previous week

[Anonymous]

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

[HotChilli · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[HotChilli]

"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