Calculate the previous month value with the same date range

[bimagty]

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 🙂

[ValtteriN · Accepted solution]

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/

[v-jingzhang · Accepted solution]

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.

[monojchakrab]

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!

[bimagty]

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

[ValtteriN · Accepted solution]

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/

[bimagty]

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).