Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kym_EVO
Frequent Visitor

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 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:

WeekLocationLDC Licenced OccupancyLDC Actual Occupancy
27/06/2021Location 1550490
27/06/2021Location 2630504
27/06/2021Location 3465435
20/06/2021Location 1                                  550                                  481
20/06/2021Location 2                                  630                                  506
20/06/2021Location 3                                  465                                  430

 

There is a lookup table (Centre Details) for the locations that show when they were purchased (Settlement Date):

LocationSettlement Date
Location 129/11/2019
Location 229/11/2019
Location 320/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.

 

Kym_EVO_0-1633493097486.png

 

Thanks for any help.

1 ACCEPTED SOLUTION

@Kym_EVO 

 

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]
)
)
)

Please @mention me in your reply if you want a response.

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

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

@Kym_EVO 

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]
)
)
)

Please @mention me in your reply if you want a response.

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

@Kym_EVO 

 

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]
)
)
)

Please @mention me in your reply if you want a response.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.