Forum Discussion

Kym_EVO's avatar
Kym_EVO
Frequent Visitor
4 years ago
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 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.

 

 

Thanks for any help.

Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

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

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    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]
    )
    )
    )
    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      4 years ago

      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]
      )
      )
      )
      • Kym_EVO's avatar
        Kym_EVO
        Frequent Visitor
        4 years ago

        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!