Caculate same period last year

[nmhung49]

I have Data table with date 01/01/2020 to 05/31/2021 and DimDate with date 01/01/2020 to 12/31/2021

When I create Dax Same Period Last Year for result 98 is incorrect. It is correct 13

Please help me the formula in this case

Thanks

Link File

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Thejeswar]

Hi @nmhung49 ,

Change the SPLY formula as follows. Also I am not really sure, why you need a separte Date Dimension Table for this functionality. Change the formula to use Date from the same table as given below

SPLY = CALCULATE(SUM(Data[Sale]),SAMEPERIODLASTYEAR(Data[Date]))

This will change the Output from 98 to 13.

Why do you expect to see 11..which the Sales for Same period last year is 13

PFB screengrab

[nmhung49]

@ryan_mayu @amitchandak 

Sorry It is show 13, I separte DimDate Table because this is the primary key table that is related to other tables I use this table to control year, month, day

[ryan_mayu]

@nmhung49 

Pls try this

SPLY = 
VAR _MAX=MAX('Table'[Date])
RETURN CALCULATE(SUM('Table'[Sale]),FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(_MAX)-1&&MONTH('Table'[Date])<=MONTH(_MAX)))

pls see the attachment below

[ryan_mayu]

@nmhung49 

why last year is 11? from jan to May is 13. 

[nmhung49]

I'm sorry show 13 not 11, I want to Card 1 st show 30 and Card 2nd show 13 with (Picture #1) because Actual Data have Date 01/01/2020 to 31/05/2021 but DimDate have Date 01/01/2020 to 31/12/2021

[ryan_mayu]

@nmhung49 

pls try this

Measure = 
VAR _max=max('Table'[Date])
return sumx(FILTER('Table','Table'[Date]=_max),'Table'[sales])

[amitchandak]

@nmhung49 , I check out total of year 2020 is 98. As you have seleced year, That will give you year total.

Also marked DimDate table as date table(option on right on table) and createcolumsn for Month, Yeat etc

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[nmhung49]

Thanks for you repply

If slicer selected 2021 and Month not selected. I want Card Current Year show 30 and Card Same Period Last Year show 11

Thanks