topic Re: Last year sales is not matching when compared in DAX Commands and Tips

Last year sales is not matching when compared

Alex_Hasan — Fri, 13 May 2022 13:29:05 GMT

Hello, I was comparing total sales of this year vs last year. But look at the picture, In 2011, the last year sales is not matching with 2010. This is my dax formula

sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

What's the problem actually?

Re: Last year sales is not matching when compared

HotChilli — Fri, 13 May 2022 15:22:23 GMT

I suspect this is to do with getting a date period shifted back 1 year but what you really want is to subtract one from the Fiscal year filter. There will be differences of a few days which shows as a difference in the table .

So rewrite the measure without sameperiodlastyear but subtracting 1 from the fiscal year

Re: Last year sales is not matching when compared

Alex_Hasan — Fri, 13 May 2022 15:39:50 GMT

Hi HotChilli , thanks for your response. This is the result showing.

Re: Last year sales is not matching when compared

HotChilli — Fri, 13 May 2022 15:51:11 GMT

I was trying to give the algorithm rather than explicit directions for the measure.

Filter arguments are of the form : column = some value (which uses an aggregation).

You have part of the solution but not all of it.

Re: Last year sales is not matching when compared

HotChilli — Fri, 13 May 2022 16:52:28 GMT

I disagree with your assessment. I'm not going to write it for you but I'm trying to give you encouragement.

See my previous post for hint.

Re: Last year sales is not matching when compared

HotChilli — Fri, 13 May 2022 17:35:46 GMT

Was that the right file (I think you might have deleted it now)?
Anyway, try this

Sales PreviousYear = CALCULATE([Total Sales] , DimDate[FiscalYear] = MAX(DimDate[FiscalYear]) -1)

Re: Last year sales is not matching when compared

Alex_Hasan — Fri, 13 May 2022 17:46:06 GMT

Yeah, I have deleted it. I found the problem. I mistakenly chose Fiscal Year instead of Calender year (I could not notice it). Now everything is all right with the sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR(DimDate[Date])) formula.

Your last code didn't work actually. But I am grateful to you because you tried to help me a lot. Thank you so much.