cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prasy14 Regular Visitor
Regular Visitor

BOP Rolling Average with Fiscal Calendar

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/

 

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: BOP Rolling Average with Fiscal Calendar

hi, @prasy14

After my test, there is a simple for you to refer to

Add a New quick measure as below:

7.JPG8.JPG

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:

10.JPG

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

11.JPG

 

Result:

12.JPG

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.

https://www.dropbox.com/s/7ysv0537uv55ofm/BOP%20Rolling%20Average%20with%20Fiscal%20Calendar.pbix?dl...

 

Best Regards,

Lin

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
prasy14 Regular Visitor
Regular Visitor

Re: BOP Rolling Average with Fiscal Calendar

Hi ,
Thanks for detailed explanation and powerbi file as well.
1. Can we do same rolling calculation using BOP measure which is
Mentioned on my main post as this average needs to be calculated on BOP not
Revenue column (1/12018 -1000,1/2/2018-3099) BOP will get -1000
For month of January.
2. How can we end rolling when fiscal year ends.
3. for quarter is there a way to calculate without applying filers,
According to present requirement user will use this data on
Excel by utilizing anlyse the data in excel in pivot table format
( using all calculated datasets). Columns on excel is date hierarchy(year->quarter->month) drill down
Thanks in advance
Community Support Team
Community Support Team

Re: BOP Rolling Average with Fiscal Calendar

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

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 336 members 3,822 guests
Please welcome our newest community members: