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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Reroo
Frequent Visitor

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 periods with the corresponding deviation. In creating my model I mostly used the technic described here. I tried to ask a question there but my question keeps getting marked as spam and as a result it wouldn't get posted. 

 

So basically I am calculating sales for locations that were open across all selected timeframes. A location is defined as open if it has been open for the whole year (12 months). Furthermore, I would also like to calculate sales of previous year so that I could use it to reference in my variance measure. 

 

I have my calendar, accounts and locations (accounting units) table. 

For dynamic calculation of sales per location I created a measure that would look for locations that were open across all timeframes selected and then use them as filter in sales calculation. 

 

My goal is to create a measure for previous period. Ideally, it would be different depending on the years chosen in slicer since I would like to be able to do a dynamic comparison between a current year with the previous periods. However, my priority is to be able to calculate the previous year value, but would really appreciate if anyone had any suggestions about the best way of this kind of calculation. However, I haven't been able to achieve this and have been struggling for over a month now.

 

The sample data and model design could be found here

 

I am quite open to any feedback and would really appreciate any help here.

1 ACCEPTED SOLUTION
Reroo
Frequent Visitor

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

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @Reroo ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
CALCULATE (
    [LFL EBITDA Actual],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year]
            = ( SELECTEDVALUE ( 'Calendar'[Year] ) - 1 )
            && 'Calendar'[Month] IN VALUES ( 'Calendar'[Month] )
    )
)

vpollymsft_0-1652942108237.png

If I have misunderstood your meaning, please provide more details with your desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

Reroo
Frequent Visitor

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.