Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I am working on a measure that will return MTD sales for completed days in the month vs the same amount of days in the previous month. For whatever reason if I use this formula it doesn't work as expected.
Then I found curbal's video https://www.youtube.com/watch?v=-xBYtOVyMTs&t=308s and made some changes to my formula to exclude the last date in my calendar table (today) and it works perfectly.
When I try to change this formula to include the last date, it returns the total days in November again.
Any idea what I'm doing wrong, or another way to code around this? The reason I like the simplicity is that for completed months in the past, works like PREVIOUSMONTH formula, but for the current month, it works by comparing apples to apples (not previous completed MTD total vs current month completed days).
Solved! Go to Solution.
Hi,
To write a measure that will return MTD sales for completed days in the month vs the same amount of days in the previous month, you do not need to create another Date table. Assuming you already have a Calendar table which only goes till the data till which there is sale, write these measures.
Total sales = sum(data[sale])
Total sales in same period last month = if(max(calendar[date])<eomonth(max(calendar[date]),0),calculate([total sales],datesbetween(calendar[date],edate(min(calendar[date]),-1),edate(max(calendar[date]),-1))),calculate([total sales],previousmonth(calendar[date])))
To your visual/filters/slicers, drag date and other other date field (month/year) from the Calendar Table.
Thanks again for your help. Any idea how to do Quarter To Date vs same time last quarter?
You are welcome. Try the STARTOFQUARTER() and ENDOFQUARTETR() DAX functions.
Thanks Ashish.
Any idea why the DateADD (DATESMTD(Date[Date]),-1,MONTH) isn't working as expected? Seems like a bug?
You are welcome. I do not use the DATEADD() function. Did my solution work?
Hi,
To write a measure that will return MTD sales for completed days in the month vs the same amount of days in the previous month, you do not need to create another Date table. Assuming you already have a Calendar table which only goes till the data till which there is sale, write these measures.
Total sales = sum(data[sale])
Total sales in same period last month = if(max(calendar[date])<eomonth(max(calendar[date]),0),calculate([total sales],datesbetween(calendar[date],edate(min(calendar[date]),-1),edate(max(calendar[date]),-1))),calculate([total sales],previousmonth(calendar[date])))
To your visual/filters/slicers, drag date and other other date field (month/year) from the Calendar Table.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |