topic Re: How to create cumulative weighted average for every month in DAX in DAX Commands and Tips

How to create cumulative weighted average for every month in DAX

Elwira_A — Wed, 27 Jul 2022 09:26:38 GMT

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!

Re: How to create cumulative weighted average for every month in DAX

amitchandak — Wed, 27 Jul 2022 14:55:07 GMT

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.

Re: How to create cumulative weighted average for every month in DAX

Elwira_A — Wed, 27 Jul 2022 16:17:13 GMT

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!

Re: How to create cumulative weighted average for every month in DAX

v-chenwuz-msft — Mon, 01 Aug 2022 10:12:39 GMT

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.

Re: How to create cumulative weighted average for every month in DAX

Elwira_A — Mon, 01 Aug 2022 10:45:29 GMT

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