Forum Discussion
CALCULATE date range DAX not displaying
Hey,
I have got the following two DAX formulas, one to show me the sum of volume for the last 12 months of data I have "MAT TY (Volume)" , and the other is supposed to show the 12 months before that "MAT YA (Volume)". My data goes up to October 2021 at the monent, so the first formula should show November 2020 to October 2021, and the twelve months before that would be November 2019 to October 2020.
MAT TY (Volume) =
VAR twelvemonthsago = DATE(YEAR(MAX(DATA[Date]))-1,MONTH(MAX(DATA[Date]))+1,1)
VAR currentmonth = MAX(DATA[Date])
RETURN
CALCULATE([Total Vol (L)],
FILTER((Dates),
Dates[Date] >= twelvemonthsago &&
Dates[Date] <= currentmonth))+0
MAT YA (Volume) =
VAR twelvemonthsago = DATE(YEAR(MAX(DATA[Date]))-1,MONTH(MAX(DATA[Date]))+1,1)
VAR twentyfourmonthsago = DATE(YEAR(MAX(DATA[Date]))-2,MONTH(MAX(DATA[Date]))+1,1)
RETURN
CALCULATE(DATA[Total Vol (L)],
FILTER((Dates),
Dates[Date] >= twentyfourmonthsago &&
Dates[Date] <= twelvemonthsago))+0
They both seem to work, but only "MAT TY (Volume)" works when using the Date axis, please see below;
| Date | currentmonth | twelvemonthsago | twentyfourmonthsago | Total Vol (L) | MAT TY (Volume) | MAT YA (Volume) |
| 01/01/2020 00:00 | 01/01/2020 00:00 | 01/01/2019 00:00 | 01/02/2018 00:00 | 5461236 | 5461236 | 0 |
| 01/02/2020 00:00 | 01/02/2020 00:00 | 01/02/2019 00:00 | 01/03/2018 00:00 | 4531530 | 4531530 | 0 |
| 01/03/2020 00:00 | 01/03/2020 00:00 | 01/03/2019 00:00 | 01/04/2018 00:00 | 4959414 | 4959414 | 0 |
| 01/04/2020 00:00 | 01/04/2020 00:00 | 01/04/2019 00:00 | 01/05/2018 00:00 | 5513649 | 5513649 | 0 |
| 01/05/2020 00:00 | 01/05/2020 00:00 | 01/05/2019 00:00 | 01/06/2018 00:00 | 8499574 | 8499574 | 0 |
| 01/06/2020 00:00 | 01/06/2020 00:00 | 01/06/2019 00:00 | 01/07/2018 00:00 | 4387728 | 4387728 | 0 |
| 01/07/2020 00:00 | 01/07/2020 00:00 | 01/07/2019 00:00 | 01/08/2018 00:00 | 4878982 | 4878982 | 0 |
| 01/08/2020 00:00 | 01/08/2020 00:00 | 01/08/2019 00:00 | 01/09/2018 00:00 | 6837669 | 6837669 | 0 |
| 01/09/2020 00:00 | 01/09/2020 00:00 | 01/09/2019 00:00 | 01/10/2018 00:00 | 8339018 | 8339018 | 0 |
| 01/10/2020 00:00 | 01/10/2020 00:00 | 01/10/2019 00:00 | 01/11/2018 00:00 | 8480410 | 8480410 | 0 |
MAT TY isn't displaying the last 12 months, and MAT YA is showing "0"
Any help you be great, thanks
J
magnify-bi-com change
FILTER((Dates), to FILTER(ALL(Dates),✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
4 Replies
magnify-bi-com change
FILTER((Dates), to FILTER(ALL(Dates),✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Legend, thanks, I had to change the date reference to the DATA table also, but this worked, thanks so much
Just one thing parry2k
I can't filter those measures with slicers now, how do I change it so Slicers will work?
magnify-bi-com change ALL to ALLSELECTED
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
