DAX for calculating a running average over defined days, including filtering out dates

[dsalony]

I have a set of data that has dates associated with it.  I want to display a matrix that has presents a DAX measure that is a rolling average of a daily number, but that alllows (for displaying) filtering off certain dates (e.g. weekend days).  So if the rolling day count is 10, and the first date displayed is a Friday, then the average would be over the trailing 10 days to the Wed of the prior week, BUT removing the two weekend days that are a part of that range; the next date down on Thu would average 10 days back to the preceeding week's Tue, again filtering off Sat and Sun, and so on.  

My attempt is below, but it is not working, as it is not filtering off all the weekend days (which I have filtered OFF with a Day of Week slicer).  This results in the rolling ave each date being artifically low because it is including all Sat and Sun.  Other iterations ended up not applying the lag day (Time Lag (Days) below)parameter at all.  I am thinking there is a simple filter taxonomy that I am just missing?

Ave Badged 2 =

CALCULATE(AVERAGEX(VALUES(EDW_TIME_HIERARCHY[Date (long)]),[Total Badged]),

DATESINPERIOD(EDW_TIME_HIERARCHY[Date (long)],[Period End],-SELECTEDVALUE('Time Lag (Days)'[Time Lag (Days)]),DAY),

ALLSELECTED(EDW_TIME_HIERARCHY[Day of Week]),

REMOVEFILTERS(EDW_TIME_HIERARCHY[FISCAL_MONTH_NAME]),

REMOVEFILTERS(EDW_TIME_HIERARCHY[Fiscal Qtr]),

REMOVEFILTERS(EDW_TIME_HIERARCHY[Date (long)]))

[dsalony]

hey.  thanks so much for responding... unlike in the simple model you attached, I didn't have the data/value that was being averaged in the same table as the one being used for the date field; I was using a separate table for my dates (model is much more complex, with mulitple table relying on one date table that I use in a single direction filtering context; in my DAX formula, I moved all my references to the table that had the value being averaged, and it worked.  thanks for your help man!

[dsalony]

for clarity, I turned off the filter I had applied so that weekend days are showing... what I am trying to do is calc a running average on the Daily Badged (orange bars) column, so if I choose a 5 day rolling average, the result for the last date in the range, Wed 3/30, would average the Daily Badged values for 3/30 back through Sat, 3/26, BUT it would filter off the lower days of Sat and Sun, so the result should be 6,986 (the average of the three weekdays from Wed back to Mon), instead of the result shown as 4,366, which includes the low daily values for Sat and Sun.  Anyone have any suggestions?

[Icey · Accepted solution]

Hi @dsalony ,

I can't reproduce your issue when creating a simple model like the attchment. Could you create one meet your scenario for test? Please don't contain any sensitive information.

Reference:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.