Forum Discussion

prasy14's avatar
prasy14
Icon for Helper I rankHelper I
7 years ago

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. E...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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.

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

 

Best Regards,

Lin

 

 

 

 

 

  • prasy14's avatar
    prasy14
    Icon for Helper I rankHelper I
    7 years ago
    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
    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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