Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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]))
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 !!!
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.
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]))
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |