Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 location has been open for a year from the latest week end date.
The occupancy calculation works, and the calculation I made to determine if the location has been open for a year works, however the calulation includes the data for all the locations in the total or when I chart it, rather than only inlcuding the data for the locations that have been open for a year.
Here is an example of the data in sheet 1:
Week | Location | LDC Licenced Occupancy | LDC Actual Occupancy |
27/06/2021 | Location 1 | 550 | 490 |
27/06/2021 | Location 2 | 630 | 504 |
27/06/2021 | Location 3 | 465 | 435 |
20/06/2021 | Location 1 | 550 | 481 |
20/06/2021 | Location 2 | 630 | 506 |
20/06/2021 | Location 3 | 465 | 430 |
There is a lookup table (Centre Details) for the locations that show when they were purchased (Settlement Date):
Location | Settlement Date |
Location 1 | 29/11/2019 |
Location 2 | 29/11/2019 |
Location 3 | 20/08/2021 |
This is my current expression:
Like for Like Occ =
IF (
LASTDATE ( 'Sheet 1'[Week] ) - ( MIN ( 'Centre Details'[Settlement Date] ) ) >= 365,
DIVIDE (
SUM ( 'Sheet 1'[LDC Actual Occupancy] ),
( SUM ( 'Sheet 1'[LDC Licenced Occupancy] ) )
),
BLANK ()
What I'm trying to achieve is a graph that shows occupancy each week for locations that have been open for a year or more, but when I chart the above, it shows the result for all locations, whereas it should only show the result using the data from location 1 and 2.
Thanks for any help.
Solved! Go to Solution.
If you want to sum first, then divide, try this measure:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
If you want to sum first, then divide, try this measure:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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!
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |