How to create cumulative weighted average for every month in DAX

[Elwira_A]

Hi I have an issue,
I try to calculate cumulative weightet average for every month (days and Total already are measures)
I have data: 

And I what to have for every month:
Jan = (total * days) / days
Feb = (Jan(total*days) + Feb(total*days)) / (Jan(days) + Feb(days))
March = (Jan(total*days) + Feb(total*days) + Mar(total*days)) / (Jan(days) + Feb(days)+Mar(days))
and so on...
how it is possible in Dax?

of course I have data not in one table, but Total and Days - are already measures.

Thanks for help!

[v-chenwuz-msft · Accepted solution]

Hi @Elwira_A ,

Maybe you can try this code to do that:

Measure =
VAR _s =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'date' ), [Date] <= MAX ( 'Table'[Date] ) ),
        [Date].[Month],
        "sum*days", SUM ( 'Table'[Values] ) * [Days],
        "days", [Days]
    )
RETURN
    DIVIDE ( SUMX ( _s, [sum*days] ), SUMX ( _s, [days] ) )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Elwira_A]

It is great! I was looking for it! Thank you!! 🙂

[amitchandak]

@Elwira_A , Try a measure like below. I am using date table

Cumm = CALCULATE(averageX(values('Date'[Date]),calculate(SUM(Sales[Sales Amount]))) ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Elwira_A]

Hi,
I also use a date table.
But it isnt a solution, only for Total it is working, but when I multiply Total * Days = I have wrong data.
so the question is how to do it to get good results.

Thanks!