topic Re: Calculate the previous month value with the same date range in DAX Commands and Tips

Calculate the previous month value with the same date range

bimagty — Tue, 01 Mar 2022 06:59:36 GMT

Hello,

I have problem with defining dax for calculating the sum of previous month, the conditions:

- This month is February and the data is only available until 19 February, I have calculated this month ongoing sum which is from 1-19 February as selected month measure.

- I want to calculate the same period in previous month but with the same date range as I have now, i.e. sum of sales 1-19 February vs. sum of 1-19 January.

Ive tried to use this formula (shown below), but it calculates the entire sum of sales in January instead of 1-19 January only.

What step do I miss? Really need your help, thanks in advance guys 🙂

Re: Calculate the previous month value with the same date range

ValtteriN — Tue, 01 Mar 2022 08:13:16 GMT

Hi,

Try switching the order of your fucntions:

Measure = CALCULATE(SUM(Aggregation[Duration(Secs)]),datesmtd(DATEADD('Calendar'[Date],-1,MONTH)))

This will calculate MTD amounts for the previous month (using the same date range).

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: Calculate the previous month value with the same date range

bimagty — Tue, 01 Mar 2022 08:22:06 GMT

Hi,
Thanks for the reply, but unfortunately I still get the same result as before, it calculates the total of 1 month instead of only selected range of date.

test is the measure following your suggestion, and the Previous Month Revenue is the total revenue in a full month (January).

Re: Calculate the previous month value with the same date range

v-jingzhang — Fri, 04 Mar 2022 07:51:49 GMT

Hi bimagty

You can try these measures. I attached a sample pbix at bottom.

This Month = VAR _endDate = MAX(Revenues[Date]) VAR _startDate = EOMONTH(_endDate,-1)+1 RETURN CALCULATE(SUM(Revenues[Revenue]),DATESBETWEEN('Calendar'[Date],_startDate,_endDate))

Previous Month = VAR _maxDate = MAX(Revenues[Date]) VAR _startDate = EOMONTH(_maxDate,-2) + 1 VAR _endDate = _startDate + DAY(_maxDate) - 1 RETURN CALCULATE(SUM(Revenues[Revenue]),DATESBETWEEN('Calendar'[Date],_startDate,_endDate))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Re: Calculate the previous month value with the same date range

bimagty — Mon, 07 Mar 2022 09:39:51 GMT

Yeay, great solution!
Thank you very much, now it works well.

Re: Calculate the previous month value with the same date range

monojchakrab — Sun, 24 Jul 2022 16:14:49 GMT

Great solution v-jingzhang - this is the best and works for me amongst all the hacks I have gone thru so far on the web.

Really helped me out of a tacky situation!