Forum Discussion
Solved
Like for Like Calculation Based on Weekly Data
Hi all, Relatively new to DAX and building in PowerBI and would appreciate any help. We have multiple locations and I'm trying to calculate a weekly occupancy calculation depending on whether the...
If you want to sum first, then divide, try this measure:
Like for Like Occ 3 =DIVIDE(SUMX('Sheet 1', IF(RELATED('Centre Details'[Settlement Date]) + 365 <= MAX(DimDate[Date]),'Sheet 1'[LDC Actual Occupancy])),SUMX('Sheet 1', IF(RELATED('Centre Details'[Settlement Date]) + 365 <= MAX(DimDate[Date]),'Sheet 1'[LDC Licenced Occupancy])))
LASTDATE returns a table, so not likely what you need in this case: https://dax.guide/lastdate/
Also, you need to provide ROW CONTEXT for the Location that you're wanting to check the settlement date against.
This measure uses the Sheet 1 Row context, let me know if it returns your expected results:
Like for Like Occ 2 =
SUMX('Sheet 1'
, IF(RELATED('Centre Details'[Settlement Date]) + 365 <= MAX(DimDate[Date])
, DIVIDE('Sheet 1'[LDC Actual Occupancy], 'Sheet 1'[LDC Licenced Occupancy]
)
)
)
If you want to sum first, then divide, try this measure:
Like for Like Occ 3 =
DIVIDE(
SUMX('Sheet 1'
, IF(RELATED('Centre Details'[Settlement Date]) + 365 <= MAX(DimDate[Date])
,'Sheet 1'[LDC Actual Occupancy]
)
)
,
SUMX('Sheet 1'
, IF(RELATED('Centre Details'[Settlement Date]) + 365 <= MAX(DimDate[Date])
,'Sheet 1'[LDC Licenced Occupancy]
)
)
)
Thanks very much AllisonKennedy. This second solution worked as it's a percentage calculation so the other solution summed the percentages but this solution gives a total percentage.
Appreciate the help!
