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.