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
Community ChampionYou 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