Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply

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;

 
DatecurrentmonthtwelvemonthsagotwentyfourmonthsagoTotal Vol (L)MAT TY (Volume)MAT YA (Volume)
01/01/2020 00:0001/01/2020 00:0001/01/2019 00:0001/02/2018 00:00546123654612360
01/02/2020 00:0001/02/2020 00:0001/02/2019 00:0001/03/2018 00:00453153045315300
01/03/2020 00:0001/03/2020 00:0001/03/2019 00:0001/04/2018 00:00495941449594140
01/04/2020 00:0001/04/2020 00:0001/04/2019 00:0001/05/2018 00:00551364955136490
01/05/2020 00:0001/05/2020 00:0001/05/2019 00:0001/06/2018 00:00849957484995740
01/06/2020 00:0001/06/2020 00:0001/06/2019 00:0001/07/2018 00:00438772843877280
01/07/2020 00:0001/07/2020 00:0001/07/2019 00:0001/08/2018 00:00487898248789820
01/08/2020 00:0001/08/2020 00:0001/08/2019 00:0001/09/2018 00:00683766968376690
01/09/2020 00:0001/09/2020 00:0001/09/2019 00:0001/10/2018 00:00833901883390180
01/10/2020 00:0001/10/2020 00:0001/10/2019 00:0001/11/2018 00:00848041084804100

 

MAT TY isn't displaying the last 12 months, and MAT YA is showing "0"

 

Any help you be great, thanks

 

J

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@magnify-bi-com change 

FILTER((Dates),

to

FILTER(ALL(Dates),

 

 

Follow us on LinkedIn and YouTube.gif 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.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@magnify-bi-com change ALL to ALLSELECTED

 

 

Follow us on LinkedIn and YouTube.gif 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.

parry2k
Super User
Super User

@magnify-bi-com change 

FILTER((Dates),

to

FILTER(ALL(Dates),

 

 

Follow us on LinkedIn and YouTube.gif 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.

Just one thing @parry2k 

 

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.