Running total of complex measure

[Teaman]

Hi All,

I have data and measure as below which returns the following table:

The 'AVG per Day by month group' measure returns the total volume based on the day of the month and is the same for each month.

What I want is a running total for when a month is selected. If graphed against the date and May is selected, the graph will show a running total as below etc.

I have tried several different types of running total measures and combinations but a lot of them are just returning the same 'AVG per Day by month group' value.  Note: This is just test data, my actual data source also includes a seperate date table.

Any Help is greatly appreciated.

Thank you!

(below measure works fine)

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 )

        )

    )

Data table: 
Date, Product ID, Day of Month, Volume

01 March 2023	1	1	0
01 March 2023	2	1	0
01 April 2023	1	1	0
01 April 2023	2	1	60
01 May 2023	1	1	10
01 May 2023	2	1	10
02 March 2023	1	2	5
02 March 2023	2	2	0
02 April 2023	1	2	30
02 April 2023	2	2	0
02 May 2023	1	2	20
02 May 2023	2	2	20
03 March 2023	1	3	5
03 March 2023	2	3	16
03 April 2023	1	3	20
03 April 2023	2	3	15
03 May 2023	1	3	8
03 May 2023	2	3	31

[Anonymous]

Hi  @Teaman ,

Here are the steps you can follow：

1. Create measure.

Measure =
SUMX(
    FILTER(ALLSELECTED('Table'),    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[AVG per Day by month group])

2. Result:

Best Regards,

Liu Yang

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

[Teaman]

Hi @Anonymous 

Thanks for your response! When I use this with my actual data (which also includes a date table) it doesn't seem to work and instead shows the running total of Data[Volume] instead of the whole 'AVG per Day by month group' measure. I've tried other ways to calculate the running total of the above measure but it always seems to show the RT of Data[Volume] instead. Do you know what needs to change?

Note: my report also includes a Year-Month column which is used a s a filter on the page.

Thanks,

[Mahesh0016]

@Teaman Please share your Expected output in table !Thank You!!

[Teaman]

Hi @Mahesh0016  ,

The below table is basically what I'm after. A running total of the 'AVG per Day by Month' measure that resets every month. The running total is going to be used with a month filter anyway so it doesn't really need to be grouped by month, it can just be a running total of all the data within the 'AVG per Day by month' measure.

Thank you

[Mahesh0016]

@i HOPE THIS HELPS YOU!THANK YOU!!

Running Avg =

TOTALMTD ( SUM ( 'Table'[Avg] ), 'Table'[Date] )