Average Values - Last 21 Working Days

Hello everyone,

Reaching out with a question which initially seemed a simple question, but it turned out a nightmare. :slight_smile:
I got a request to show on a chart the evolution by taking into consideration the last 21 working days for which data was extracted.

This means for each Report Date, I should go back with 21 working days for which we got data and do the cumulative average on the Values.

Data model looks like this:

Dummy data PBI file:
Dax Test.pbix (95.4 KB)

Any tips & guidance on how to do this? :slight_smile:

Hi @michaelsampsonidis !

I was looking at your question and I think you can use the Moving Averages Formula.

I think these videos will definitely help you with the “21 Working Day” question.


Also, here’s a picture of the formula.

image

Let me know if this works for you.

Cheers!

Hi Yeriel,

I’ve tried with the time intelligence, I will try it out also with DatesInPeriod, so far I’ve tried it with DateAdd and also indexing the report dates.
My struggle was to find a method which actually covers to check when rolling back 21 working days, if we have data for all those 21 days or we need to add more working days to reach the 21 days with data (report date).

Have a nice day ahead! :slight_smile:

Hi @michaelsampsonidis

In regards to the 21 Working Days, I added two formulas to your file that may help you.

  1. The SUM of all the values. Even though it is a simple formula, it is always better to create a formula for it, that way you can refer it into more formulas later on.
    Total Values =
    SUM( FactValues[Value] )

  2. The 21 Woking Days Average Formula:
    21 Working Day Average =
    AVERAGEX(
    DATESINPERIOD(
    dimDate[Date],
    LASTDATE( dimDate[Date] ),
    -21,
    DAY ),
    [Total Values])

It looks like this:

Note: The AverageX formula only counts the days that have values for the average expression.
In this case, the days 03.10.2020 & 04.10.2020 don’t have values thus the average value remains the same as 02.10.2020.

Let me know if this works for you.

Cheers!

Hello @michaelsampsonidis,

I came up with a solution that is pretty much the same as @Yeriel. I updated your dimDate table. Here is a copy of the file that I worked on.

EDNA- 21 Day Average.pbix (124.8 KB)

Thanks
Jarrett

1 Like

Hi @michaelsampsonidis
I think when we deal with working days we cannot rely on typical time intelligence formulas, the reason is that we can’t just subtract 21 days, we need to subtract 21 WORKING days! Furthermore, time intelligence uses continuous dates calendar, so using time intelligence would lead to a mistake.

Therefore my solution requires building a support table that contains the last 21 Working days. This table is not related so you can use the following measure

WorkingDaysValue =
VAR WDay21days_ago =
    MIN ( LastWorkingDates[Report Date] )
RETURN
    CALCULATE (
        SUM ( FactValues[Value] ),
        FILTER (
            dimDate,
            dimDate[DayOfWeekName] <> "Sunday"
                && dimDate[DayOfWeekName] <> "Saturday"
        ),
        FILTER ( dimDate, dimDate[Date] > WDay21days_ago )
    )

The table is created using the following formula

LastWorkingDates =
TOPN (
    21,
    DISTINCT (
        FILTER (
            SUMMARIZE ( FactValues, FactValues[Report Date], dimDate[DayOfWeekName] ),
            dimDate[DayOfWeekName] <> "Sunday"
                && dimDate[DayOfWeekName] <> "Saturday"
        )
    ),
    FactValues[Report Date]
)

I hope this works for you. This is my first post here

Hi @diego,

You are on the right track thinking that typical time intelligence does not apply. However, in my dimDate table, I have a column that is called IsWorkingDay that allows typical time intelligence to be applied. Here is my revised measure for the 21 Days Average:

                21 Days Average =
                CALCULATE (
                    AVERAGEX (
                        DATESINPERIOD ( dimDate[Date], LASTDATE ( dimDate[Date] ), -21, DAY ),
                        [Total Value]
                    ),
                    FILTER ( dimDate, dimDate[IsWorkingDay] = TRUE () )
                )

Here is a copy of the file. I also included your measure and table as a reference. Page 2 is where I have your measure and my revised measure shown.

EDNA- 21 Day Average.pbix (126.5 KB)

Thanks
Jarrett

2 Likes

Yes, that’s the idea.

My bad, I forgot to upload the file I worked on.21 Woking Days.pbix (99.7 KB)

Hi @michaelsampsonidis, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

@JarrettM, @michaelsampsonidis, @Yeriel

Hello

Sorry mates I was busy at the weekend so I wasn’t able to develop the explanation.
Unfortunately, using Calculate (filtering working days) does not work in this case. You can compute the solution manually in Excel to test it.
The solution is to compute an AVERAGEX iterating over a working days table.

WorkingDaysOverTable =
VAR CurrentDate =
SELECTEDVALUE ( dimDate[Date] )
VAR FTable =
FILTER (
ALL ( LastWorkingDates ),
LastWorkingDates[Report Date] <= CurrentDate
)
VAR FilterTopTable =
TOPN ( 21, FTable, [Report Date] )
RETURN
AVERAGEX ( FilterTopTable, [Total Value] )

I have included the pbi file including the comparison with the Calculate +filter solution.

The reason why Calculate+filter does not work is interesting (If you know, please let us know). I know that Microsoft does not recommend using time intelligence for a non-continuous dates table. therefore. it is a good practice to solve this kind of problem using logic functions.EDNA- 21 Day Average Iterate over working days table.pbix (134.5 KB)First Help.xlsx (50.7 KB)

2 Likes

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!