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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ARLFG
Frequent Visitor

DAX measure to calculate full year data regardless of date

Hello everyone!

Been spun out on a tricky one here and have exhausted my resources so I'm hoping to this gets some traction.

 

I'm trying to get a DAX Measure that will return the full year's SUM of Total $ regardless of the date. This needs to also show that Total for dates that don't have data.

 

To clarify with an example, I have a Fact table that has a Transaction date of 2/2/2023 (Q1) with an amount of $100, but no other transactions for 2023. How can I create a Measure that will return $100 for all 2023 dates in table that has DimDate selection in the rows? I have tried many interations of calculate/all. 


What I'd like to see is:

Quarter (from Dim Date)                Total Amount

Q1-2023                                          $100
Q2-2023                                          $100

Q3-2023                                          $100

Q4-2023                                          $100

 

Thank you!
Andrew

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You have not shared any data to work with so this is pure guesswork.  Try this measure

Measure = calculate(sum(Data[Amount]),allexcept(calendar,calendar[year]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

ARLFG
Frequent Visitor

I tried the above and ended up with a suggestion to a similar formula:

Calculate(sum(Data[Amount]),all(Calendar),Calendar[Year] = MAX (Calendar[Year]

 

which aligns with my end goal. 


Thank you for the guidance @Ashish_Mathur !!!

View solution in original post

3 REPLIES 3
ARLFG
Frequent Visitor

I tried the above and ended up with a suggestion to a similar formula:

Calculate(sum(Data[Amount]),all(Calendar),Calendar[Year] = MAX (Calendar[Year]

 

which aligns with my end goal. 


Thank you for the guidance @Ashish_Mathur !!!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

You have not shared any data to work with so this is pure guesswork.  Try this measure

Measure = calculate(sum(Data[Amount]),allexcept(calendar,calendar[year]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.