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