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 All,
I need help to calculate the rolling present year months average for Beginning for Period Inventory. Below is my calculation to get Beginning of Period
Our Fiscal Calendar start from Feb. Each Quarter in Fiscal Calendar has 3 months ex: Q1 has Feb,March,April.
But According to requirement Q1 BOP - Should average of Feb, March, April, And May. Similarly for Q2 -May,June ,July,Aug
Firstly How can i achieve rolling monthly average,Second for quarter How can i calculate differently
BOP =
VAR FirstDateForPeriod = FIRSTDATE('binventory'[Idate])
RETURN CALCULATE(SUM('binventory'[irevenue]),'bcalendar'[bdate]= FirstDateForPeriod)
For Example
BOP BOP12SUM BOP12AVG
Feb - 1000 1 000 1000
March - 2000 3000 1500
April - 3000 6000 2000
May - 4000 10000 2500
I have followed below link but was unsucessful to get the output
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
hi, @prasy14
After my test, there is a simple for you to refer to
Add a New quick measure as below:
irevenue rolling average = IF( ISFILTERED('bcalendar'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('bcalendar'[Date].[Date]) VAR __DATE_PERIOD = DATESBETWEEN( 'bcalendar'[Date].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)), __LAST_DATE ) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('bcalendar'), 'bcalendar'[Date].[Year], 'bcalendar'[Date].[QuarterNo], 'bcalendar'[Date].[Quarter], 'bcalendar'[Date].[MonthNo], 'bcalendar'[Date].[Month] ), __DATE_PERIOD ), CALCULATE(SUM('binventory'[irevenue]), ALL('bcalendar'[Date].[Day])) ) )
Result:
and then if you want Fiscal year divide into three groups and reset Rolling Average for each group
you do these as below:
Step1:
Add a Fiscal Quarter group column
Fiscal group = IF(bcalendar[Month] in {2,3,4,5},"A",IF(bcalendar[Month] in {6,7,8,9},"B",IF(bcalendar[Month] in {10,11,12,1},"C")))
Step2:
Drag the field Fiscal group into the visual and drag the field irevenue into visual level filter and set filter is not blank
Result:
for example
in the left picture: 2018/06=(2000+3000+4000+5000)/4=3500
in the right picture: 2018/06=5000/1=5000
here is my pbix, please try it.
Best Regards,
Lin
hi, @prasy14
1.I'm a little confused about your description, for what is BOP not Revenue column (1/12018 -1000,1/2/2018-3099) BOP will get -1000 For month of January,
could you please share some more sample data and the expected output? that will
help us have a better understanding of the issue.
2. We may add hierarchy column achieve it.
3. This is a simple way, It may be hard to only write a formula to achieve it.
Best Regards,
Lin
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 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |