Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |