Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic Date & Site/Location Reference in Like For Like Calculations

Hi, everyone.   I have been having trouble with performing like for like calculations. Specifically, the task is to produce like for like calculation table where I would have comparison of two per...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hey, 

     

    I figured it out. I just integrated in my measure the minimum date that I then used in time filter for calendar year:

     

    <div>

     

    Measure =
VAR MinimumDate = CALCULATE(MIN('Calendar'[Year]),ALLSELECTED('Calendar'[Year]))
VAR Result = CALCULATE (
    [LFL EBITDA Actual],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year]
            = ( MinimumDate )
            && 'Calendar'[Month] IN VALUES ( 'Calendar'[Month] )
    )
)
RETURN
    Result

     

     

    </div>

     

    Best regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey, thanks a lot. This does it.

 

I would also like to be able to choose the previous period and not be limited to only one period back. So say when I want to compare 2022 with 2019, I would like to select these two in my year slicer the measure would then dynamically calculate the sales for these two periods, that is sales for locations that were open between these two periods, whereas current year measure would refer to 2022 and previous year measure refer to 2019 while picking only the locations that were open only in these two years. 

 

Do you have an idea how one could achieve that? 

 

Thanks and best regards,

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey, 

 

I figured it out. I just integrated in my measure the minimum date that I then used in time filter for calendar year:

 

<div>

 

Measure =
VAR MinimumDate = CALCULATE(MIN('Calendar'[Year]),ALLSELECTED('Calendar'[Year]))
VAR Result = CALCULATE (
    [LFL EBITDA Actual],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year]
            = ( MinimumDate )
            && 'Calendar'[Month] IN VALUES ( 'Calendar'[Month] )
    )
)
RETURN
    Result

 

 

</div>

 

Best regards