topic Re: cumulate a mesure month by month from a startdate to an enddate in DAX Commands and Tips

cumulate a mesure month by month from a startdate to an enddate

ouafaa — Thu, 24 Feb 2022 09:40:03 GMT

I have a measure Mesure1 that I calculate according to a chosen month and year.

now I want to use this mesure to calcul another mesure.
the rule is that when I choose a periode : startdate to enddate, the new mesure "Mesure2" should accumulate the result of the mesure1 for each month from the startdate to the enddate
and finally this result should be divided by the number of months that are includes on the periode between the startdate to the enddate

How can I do this with dax ?
thank you for any help

Re: cumulate a mesure month by month from a startdate to an enddate

ValtteriN — Thu, 24 Feb 2022 10:39:12 GMT

Hi,

Try something like this:

MTD with conditions =

var _sdate = MIN('Calendar example'[Date])

var _edate = MAX('Calendar example'[Date])

var months = DISTINCTCOUNT('Calendar'[Month]) return

DIVIDE(

CALCULATE(SUM(Aggregation[Duration(Secs)]),ALL('Calendar'),'Calendar'[Date]>=_sdate,'Calendar'[Date]<=_edate,DATESMTD('Calendar'[Date])),months)

Use second calendar for the selected period and have a relationship between your fact table and calendar.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: cumulate a mesure month by month from a startdate to an enddate

ouafaa — Thu, 24 Feb 2022 19:40:02 GMT

Hello,

It did not resolve my problem, perhaps because I did not know how to implement it for my case, but it inspired me to find another solution.

I created a calculated table (month, mesure1) and after that I was able to do the sum of the mesure month by month for a choosen period.

Thank you very much for your help