DAX sum last year but some data doesn't have data in the last year

[gzai]

I have spend data displayed in a matrix like this :

Current Year :

Name	2021	2022	2023	2024	2025
SUZUKI	596	63	365	865	418
HYUNDAI	261	773	765	167	159
TOYOTA	165	843	885	229	150
DAIHATSU	726	691	602	179	802
MITSUBISHI	28	413	725	136	272
WULING	838	538	27	118	587
HONDA	996	364	309	787	78
CITROEN			712	355	87
BYD				568	117
CHERY	677	776	900	783	109
OTHER	815	136	858	909	131

and I want to show each name prev year but it can't be displayed because there are some names that don't have data in the previous year, how do I fix the measurement?

Last Year Spend =
VAR _max = MAXX(Raw, Raw[Date])
RETURN 
    CALCULATE(
        SUM(Raw[Spend]),
        DATESYTD(dateadd(Raw[Date],-1,Year)),
        Raw[Date] <= _max
    )

[gzai]

Hi @v-agajavelly ,

Thank you for the solution. 
I have used the formula : 

Last Year Spend = VAR SpendLastYear =  CALCULATE(SUM(Raw[Spend]), SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
COALESCE (SpendLastYear, 0)

Thanks.

Regards,

[v-agajavelly · Accepted solution]

Hi @gzai 

Thank you for reaching out to Microsoft Fabric Community Forum.

The issue where some names don't have data in the previous year, you need a DAX measure that gracefully handles missing prior year data while still returning blank (or 0 if you prefer) rather than failing or skipping rows.

As for my experience your current measure has some issues.

You're using DATESYTD on a DATEADD, which assumes a full calendar, if previous year values are missing, DATEADD returns nothing, you're referencing Raw[Date] <= _max directly, which is unnecessary and might not work well in matrix context, you're not preserving context correctly for Name.

Correct DAX Last Year Spend, assuming you have a proper date table (DimDate) with a relationship to Raw[Date], try below DAX:

Last Year Spend = CALCULATE(SUM(Raw[Spend]),SAMEPERIODLASTYEAR(DimDate[Date]))

This measure returns the spend for the same period in the previous year. It works even if some names have no data in prior year, they’ll return blank (which is what Power BI shows for missing values) and also works perfectly in matrix visuals.

Try to Show 0 Instead of Blank, If you want to show 0 instead of blank when data is missing. Use below dax.

Last Year Spend = VAR SpendLastYear =  CALCULATE(SUM(Raw[Spend]), SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
COALESCE (SpendLastYear, 0)

Use DATEADD if No Time Intelligence Table, If you're not using a date table, then you can use below DAX.

Last Year Spend = CALCULATE (SUM(Raw [Spend]),DATEADD(Raw[Date], -1, YEAR))

But this only works, if Raw[Date] is at daily granularity when You’re showing matrix by Year (e.g., from YEAR(Raw[Date]))

Please use a calendar/date table (best practice for all time-based DAX) and Ensure that Raw[Date] has a relationship to the DimDate[Date] column. Use SAMEPERIODLASTYEAR or DATEADD depending on your model.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Regards,
Akhil.

[SamsonTruong]

Hi @gzai ,

Please try the adjusted measure and let me know if it achieves your desired result:

Last Year Spend =
VAR _max = MAXX(Raw, Raw[Date])
VAR _year = YEAR(_max)
VAR _lastYearStart = DATE(_year - 1, 1, 1)
VAR _lastYearEnd = DATE(_year - 1, 12, 31)
RETURN
CALCULATE(
    SUM(Raw[Spend]),
    Raw[Date] >= _lastYearStart &&
    Raw[Date] <= _lastYearEnd
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson