cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aahil10
Frequent Visitor

Show Rolling Average on Daily Basis

Hello!

I'm trying to show the rolling average of a dataset on a day to day basis. I am calulcating the average operating rate (%) for a given month.  I was able to calculate the Rolling Average for a time period using the following formula:

 

Aahil10_1-1622685187027.png

 

 

My question is, Is it possible to show the the Monthly OR data on a daily basis in card form?

I want the user to be able to select A specific date : eg March 10, and see the Monthly OR of 96.7% but using a card, it will only show me the average for that one day and not the preceding days so I only see 95.9% for that date. If I dont filter the card for the day, then I only get the Overall Average of 96.3% and not the value on that specific day.

 

Ideally, I want the User to Select the date and see the rolling average change as he clicks through the dates and then on the last day, user would see 96.3% as the Monthly Rolling Average.

 

Please let me know if this is possible!

 

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This measure should work on a card visual.  Ensure that there is a Calendar table with a relationship to the Date column of your Data Table.  To your slicer, drag Date from the Calendar Table and selected a Date.

Measure1 = calculate([Assy OR],datesmtd(calendar[date]))


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

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

This measure should work on a card visual.  Ensure that there is a Calendar table with a relationship to the Date column of your Data Table.  To your slicer, drag Date from the Calendar Table and selected a Date.

Measure1 = calculate([Assy OR],datesmtd(calendar[date]))


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

View solution in original post

@Ashish_Mathur @amitchandak Thank you both for your swift response!

 

Bear with me as I'm new to this!

I didnt have a date table linked to my data, but now that I link one, It gives me a rolling average for my entire data set (Jan- Present) adn then filters by month.  Whereas I'd like my monthly rollin average to reset each month (as it was before the linking of the date table)

 

This is how my data looks now:

Aahil10_0-1622688243525.png

 

Pre- Date Table linkage:

 

Aahil10_1-1622688347692.png

 

 

So before I try any measures on this, how would I get this to work separately for each month? Or is this too advanced a feature that requires more DAX?

 

Thanks again!

Hi,

Share the link from where i can download your PBI file.


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

1

Hi,

See the Table on page "Duplicate of Page 1".  Download the PBI file from here.

Hope this helps.

Untitled.png


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

Thanks!

I think this almost solves it, but now I'd like to Display the Monthly OR value for a given date on a card. If I try in your file, I get a blank.

 

So when the a Date e.g March 9 is selected, I can display Monthly OR as 96.82%

You are welcome.  Download the PBI file from here.

Hope this helps.

Untitled.png


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

@Aahil10,This formula should rollup and filter a month level correctly

Join production date with date of a date table, Create month year column there.

 

Your formula with date table should work

 

sumx(filter(Allselected('Date'), 'Date'[Date] <=max('Date'[Date])),[Assy OR])

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!