Forum Discussion

csrapp96's avatar
csrapp96
Icon for Helper I rankHelper I
4 years ago
Solved

Rolling Average Measure (with Parameter) - Trading Days Only

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!!

 

 

Need Help
Tutorial Requests

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.