Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
what do you have in place of dates. Can you share a small sample?
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 |
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
@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.
Thanks!
@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!
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))
Link :https://www.dropbox.com/s/ok5qmabb4v50cz5/Sample%20Database%20v3_ext.pbix?dl=0
@amitchandak- This is exactly what I was trying to accomplish - thank you so much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |