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

[Khushboobarai]

https://www.kaggle.com/datasets/kyanyoga/sample-sales-data

YOU can use this data set in which for matrix visual put the status as a row , from the order date create a year month column in "mmm-yy" format  like jan-24 and use this column as a column in matrix and it has sales which will be used as Value in matrix visual.

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

[Khushboobarai]

Hii, can you help me with the below query of year 

Can you tell me How to make a year measure which shoudl have condition like even if the data doest not have value for all month like all 12 month then also the whole 12 month ka value should show by taking some of the month from previous year to complete the 12 month sereies with cureent year.
 FOR eg: for year 2025 my data has the value only for three month like jan,feb and march. and year 2024 have all jan to dec okay 

so in my matric visual i want the result to shown as 
apr-24 may -24, jun-24, july-24 ......nov-24, dec-24, jan-25, feb-25,mar-25
 I hope from the above kidn of example you get my question, so it should be like that much dynmich thta if 2025 get any new month added to data then accoridng it should show from prevoiud year till current year by considering of 12 month series

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.