Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
Hi,
Try switching the order of your fucntions:
Proud to be a Super User!
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.
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.
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!
Yeay, great solution!
Thank you very much, now it works well.
Hi,
Try switching the order of your fucntions:
Proud to be a Super User!
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |