Rolling 13 Period Margins (without calendar dates)

[MN_CS]

Hi,

I have a set of P&L data for several stores organized by absolute periods (e.g., 1-100 and not calendar dates).  My goal is to create a rolling 13 period Cost of Sales (COS) Margin line graph -- period 99 on the graph would show COS % for periods 88-99, period 100 would show the COS % for periods 89-100.

I was able to figure out how to calculate rolling 13 period margins in a matrix using a slicer to manually select the period range and this measure: COS % = Divide(sum(COS),sum(Sales)), but I cannot figure out how to create a rolling graph visual.  I have found a number of similar threads that use some time intelligence features to accomplish this, but given my data set doesn't have dates those solutions aren't ideal.  I'm a PowerBI novice so would really appreciate any guidance here

[amitchandak]

what do you have in place of dates. Can you share a small sample?

[MN_CS]

Hi - thanks for the reply.  Below is an example of the data format I have - looking to calculate rolling last 13 Abs Period COGS % for each Abs Period ended (so Abs Per 14 would be the sum of period 2-14 COS divided by the sum of period 2-14 sales, and Abs Per 15 would be the same for periods 3-15; those values aren't shown in the example table below)

Abs Period	Store	Sales	COS
14	Store1	310,914	98,077
15	Store1	290,683	99,161
16	Store1	266,681	87,022
17	Store1	256,537	82,806
18	Store1	247,376	78,202
19	Store1	253,551	83,370
20	Store1	219,785	69,567
21	Store1	138,091	51,191
22	Store1	63,267	24,917
23	Store1	63,184	20,617
24	Store1	49,317	17,552

[amitchandak]

Please refer this and pbix

Measure = 
var _min1 =(Min('Time'[Abs Period])-12 )
Var _min = If(_min1<0,0,_min1)
Var _max = Max('Time'[Abs Period])
Return
CALCULATE(SUM(Data[Sales]),filter(all(Data[Abs Period]), Data[Abs Period] >= _min && Data[Abs Period] <=_max))

Refer : https://www.dropbox.com/s/iaolkklnpmw2whj/PeriodLast12Month.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MN_CS]

@amitchandak Thanks for the reply.  For some reason, my data set is resulting in Rolling LTP Sales calculating to be the same as Net Sales for the given Abs Per.  I think this may be due to my data set having an additional table that I forgot about in my first post.  I have tried recreating your file from scratch with a sample data set, but when I try to apply the same logic to mine I don't get the result.  I think it might be related to the FILTER function in the Rolling LTP Sales measure as that's a function I'm not familiar with.

I'm linking the data and .pbix in the hopes that there's a simple fix here -- would be very appreciative if you find time to take a look.

dropbox link 

Thanks!

[MN_CS]

@amitchandak- Wanted to follow up and see if you had any other ideas on this?  I haven't had much luck elsewhere in my continued searcing of the forums, so would really appreciate any input!

[amitchandak · Accepted solution]

Because we have taken a filter from the same table. All on the column will not work. All on the table is required. I changed it to dimension. And seems like working

Rolling LTP Sales = 
var _min1 =(min('Calendar1'[Abs Per])-12)
var _min = IF(_min1<0,0,_min1)
var _max= MAX('Calendar1'[Abs Per])
Return
CALCULATE(SUM(SL_Data[Net Sales]),filter(ALL(Calendar1[Abs Per]), Calendar1[Abs Per] >= _min && Calendar1[Abs Per] <=_max))

[amitchandak]

Link :https://www.dropbox.com/s/ok5qmabb4v50cz5/Sample%20Database%20v3_ext.pbix?dl=0

[MN_CS]

@amitchandak- This is exactly what I was trying to accomplish - thank you so much for your help!