Same period last year calculation (include rows where current year sales is $0)

[iluvcoding_91]

Hello, I have a FACT table where I'm trying to sum up the current year sales and prior year sales (side by side in a matrix chart) using the sameperiodlastyear DAX function. I want the matrix to show ALL records from both years (i.e., if sales for a particular country were generated last year, but not in the current year, I want those records displayed as well). The problem is the sameperiodlastyear function will only retrieve records from the prior year ONLY IF current year sales exists. Otherwise, all prior years records that don't exist in the current year will be filtered out. Any idea on how I can modify the measures to include all sales from both years?

[Jihwan_Kim]

Hi,

I am not sure if I understood correctly about your data model., but please check the below picture and the attached pbix file.

Sales total sameperiodlastyear: =
CALCULATE (
    [Sales total current year:],
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)

[iluvcoding_91 · Accepted solution]

Thanks, it was my data model that was not producing the correct result

[v-easonf-msft]

Hi, @iluvcoding_91 

If the issue is resolved, you can close this thread by marking the relevant reply as solution.

Best Regards,
Community Support Team _ Eason