Forum Discussion
Calculate the previous month value with the same date range
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 🙂
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/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.
5 Replies
v-jingzhangCommunity Support
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.Yeay, great solution!
Thank you very much, now it works well.
monojchakrabResolver III
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!
- ValtteriN
Community Champion
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/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).
