Average Value of Previous Months by Day

[Teaman]

Hi All,

I'm got some data that covers each day from the last few months and looks like the below.  Theres different volume figures on different days but what I need is a measure that can calculate the average volume of previous months by month day.

Date	Product ID	Day of Month	Volume
01/03/2023	1	1	0
01/03/2023	2	1	0
01/04/2023	1	1	0
01/04/2023	2	1	60
01/05/2023	1	1	10
01/05/2023	2	1	10
02/03/2023	1	2	5
02/03/2023	2	2	0
02/04/2023	1	2	30
02/04/2023	2	2	0
02/05/2023	1	2	20
02/05/2023	2	2	20

Below is what I'm wanting the measure to do (when in a table with the date). For e.g.  the 2nd month day is doing (5+30+20+20)/3 =25  (The 3 is the number of months). I've been able to do this in a calculated column but I need it as a measure to keep the Product ID filter.

Date	Measure #1
01/03/2023	26.66
01/04/2023	26.66
01/05/2023	26.66
02/03/2023	25
02/04/2023	25
02/05/2023	25

I've tried a few things but can't understand why the below won't work:

Average per Month Day = CALCULATE(sum(Data[Volume]), ALLEXCEPT(Data, Data[Day of Month]))

Any help is greatly appreciated.

Thanks

[v-shex-msft]

HI @Teaman,

You can use the following measure formula to get the average volume based on month group:

AVG per Day by month group =
VAR currDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        DIVIDE ( SUM ( Data[Volume] ), COUNTROWS ( VALUES ( Data[Date] ) ) ),
        FILTER (
            ALLSELECTED ( Data ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Data] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng

[Teaman]

Hi @v-shex-msft ,

I forgot to say that the dates in my tables are in the form DD/MM/YY so what I am after is the volumne grouped by the day of the month. I managed to edit your code to show this as below. Need to check this works with a different dataset before accepting

AVG per Day by month group =

VAR currDate =

    MAX ( Data[Date] )

RETURN

    CALCULATE (

        DIVIDE ( SUM ( Data[Volume] ), COUNTROWS ( VALUES ( Data[Date] ) ) ),

        FILTER (

            ALLSELECTED ( Data ),

            YEAR ( [Date] ) = YEAR ( currDate )

                && DAY([Date]) = DAY ( currDate )

        )

    )
Thanks for your help!

[Teaman]

Hi @v-shex-msft ,

Thank you again for your help. I need to turn this measure into a running total of each day of each month (with the values of each month being the same). I've tried the standard way's of doing running totals (like the below) and a few other things but nothing has been working. Do you have any ideas?

Running Total =  CALCULATE (

    [AVG per Day by month group],

    filter(

    ALLSELECTED(Data),

    Data[Date] <= max(Data[Date])

))

[v-shex-msft · Accepted solution]

Hi @Teaman,

According to your descriptions, this expected result should be calculate by summary volume with year month group and divided with current month day count. SO I think the formula second condition should be used with month function, or they will get wrong results. (the displayed date format does not affect the Dax year/month function, they will auto extract correspond date levels to calculate except you are work with date strings which stored with text data types)

AVG per Day by month group =
VAR currDate =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        DIVIDE ( SUM ( Data[Volume] ), COUNTROWS ( VALUES ( Data[Date] ) ) ),
        FILTER (
            ALLSELECTED ( Data ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng