cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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"

J

1 ACCEPTED SOLUTION
Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

4 REPLIES 4
Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

Just one thing @parry2k

I can't filter those measures with slicers now, how do I change it so Slicers will work?

Helper I

Legend, thanks, I had to change the date reference to the DATA table also, but this worked, thanks so much

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors