Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Rolling average monthly. Prob: Considering previous year[month].

Hi,

 

I'm trying to calculate rolling average, but facing problem when year changes. 

It's considering previous year while calculation.

Finding rolling average of "Cost per pair -USD" and Costper pair usd = [Total Epenses] / [QTY]

 

Capture.PNG

It is showing .21 (Rolling Avg.) instead of .18 (Year changes, So fresh cal.), higlighted with red line. It is because of considering previous month result 

 

I used Quick measure in power bi for rolling average.

 

Rolling Avg. = IF(
ISFILTERED('Dummy_Table'[ETA]),
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('Dummy_Table'[ETA].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dummy_Table'[ETA].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dummy_Table'),
'Dummy_Table'[ETA].[Year],
'Dummy_Table'[ETA].[QuarterNo],
'Dummy_Table'[ETA].[Quarter],
'Dummy_Table'[ETA].[MonthNo],
'Dummy_Table'[ETA].[Month]
),
__DATE_PERIOD
),
CALCULATE([Cost per Pair], ALL('Dummy_Table'[ETA].[Day]))
)
)

 Expected result.

When I tried in excel it look like. It was just simply with formula. 
Instead of .21 I want .18, as year changes from 2017 to 2018.

 

Capture1.PNG

 

 

1 ACCEPTED SOLUTION

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Assuming the following, try this measure:

 

  1. There is a calendar Table; and
  2. There is a relationship from the Date column of your Dummy_Table to the Date column of the Calendar Table

 

=CALCULATE([Cost per pair - USD],DATESYTD(Calendar[Date],"31/12"))

 

If this does not get the correct answer, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur

 

Thanks for helping me out.

It work like a charm. Salute man.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yuta-msft
Community Support
Community Support

Hi dheer1688,

 

Which DAX formula are you using on excel side? And which one is your expected result?

 

Regards,

Jimmy Tao

Anonymous
Not applicable

@v-yuta-msft 

Hi,

 

I used formula in excel. 

 

Expected result :

Instead of .21 for Jan2018 it should show .18 as year changes from 2017 to 2018.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.