Rolling Average Measure (with Parameter) - Trading Days Only

[csrapp96]

Hi all! 

First off, thank you in advance for your help. For something that seems so easy, this one defintely threw me for a whirlwind because of PBI's time intelligence.

I have data I need to calculate rolling averages for EXCLUDING weekends/Holidays. See below for Data Table: 

Next I have a date table to signify a "Working Day" where "Working Day" = 2   See below for Date Table:

Next, I have the dashboard piece of it where I am trying to get a parameter for rolling days that calculates the average of the last x number of days EXCLUDING weekends/holidays (this is where I have gone crazy trying to calculate this). Here's what it looks like:

The Dax for Rolling Average: 

5SMA Measure =

AVERAGEX(

DATESBETWEEN('Date Table'[Date], LASTDATE('Date Table'[Date]) - 'SMA Variable'[SMA Variable Value] +1, LASTDATE('Date Table'[Date])),

CALCULATE(SUM(Data[Data])))

I tried filtering off that "Working Day" = 2 but had no luck. 

Is there any way to exclude weekends/holidays for my rolling average calculation? What am I missing? 

THANK YOU SO SO MUCH!!

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Can you share the download link of your PBI file.

[csrapp96]

Hi @Ashish_Mathur, I have rebuilt the dataset to attempt to solve this

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[csrapp96]

wow - absolutely amazing!! thank you @Ashish_Mathur - truly impressive

[Ashish_Mathur]

You are welcome.

[csrapp96]

hi again @Ashish_Mathur !! 

one follow-up question for your great answer - how would I display just the period where the true rolling average begins? For example, If I had a 5 day rolling average, the first 5 days would NOT display in the visual and it would start at day 6 once there is a rolling average. Same if it was a 30 day rolling average in the parameter, it would start at day 31 of data on my visual.

THANK YOU!

[Ashish_Mathur]

Hi,

If you have a 5 day rolling average, then the SMA should start apearing from Day 5 itself (not from Day 6).  You may download my PBI file from here.

Hope this helps.

 

[csrapp96]

@Ashish_Mathur wow, you are truly incredible - I aspire to code like you someday! Wish I could give it 10 thumbs up

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.