Show Rolling Average on Daily Basis

[Aahil10]

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:

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!

[Ashish_Mathur · Accepted solution]

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]))

[Aahil10]

@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:

Pre- Date Table linkage:

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!

[Ashish_Mathur]

Hi,

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

[Aahil10]

1

[Ashish_Mathur]

Hi,

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

Hope this helps.

[Aahil10]

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%

[Ashish_Mathur]

You are welcome.  Download the PBI file from here.

Hope this helps.

[amitchandak]

@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.