DAX measure to calculate full year data regardless of date

[ARLFG]

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

[Ashish_Mathur · Accepted solution]

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]))

[ARLFG · Accepted solution]

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 !!!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

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]))