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.
Hi,
I finally found the following DAX to obtain Last Years MTD item count (vs. same # of days through the month TY).
Year | MonthName | ActivateYear | MTD Item Count |
2018 | January | 2017 | 48 |
2019 | January | 2018 | 122 |
2020 | January | 2019 | 16 |
What I really want is the MTD totals for 2020 and for 2018 (same # of days for the month as TY) as well. Is there a DAX calc that can provide this? My data set looks like this (although not complete).
ActiveYear | ActivateDateReformat | ItemNbr |
2017 | 1/8/2017 0:00 | 12345 |
2017 | 1/8/2017 0:00 | 12346 |
2017 | 1/8/2017 0:00 | 12347 |
2018 | 1/11/2018 0:00 | 12348 |
2018 | 1/11/2018 0:00 | 12349 |
2018 | 1/11/2018 0:00 | 12350 |
2018 | 1/11/2018 0:00 | 12351 |
2018 | 1/11/2018 0:00 | 12352 |
2018 | 1/11/2018 0:00 | 12353 |
2019 | 1/6/2019 0:00 | 12354 |
2019 | 1/6/2019 0:00 | 12355 |
2019 | 1/6/2019 0:00 | 12356 |
2020 | 1/6/2020 0:00 | 12357 |
2020 | 1/6/2020 0:00 | 12358 |
2020 | 1/6/2020 0:00 | 12359 |
2020 | 1/6/2020 0:00 | 12360 |
Solved! Go to Solution.
did not get it completely. In the same formula you can change -1 values to go either side.
other ways you can use is
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))
did not get it completely. In the same formula you can change -1 values to go either side.
other ways you can use is
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |