Trouble with SAMEPERIODLASTYEAR function

[BH22One]

Hello,

I am attempting to use the "SAMEPERIODLASTYEAR" function in my report but can't seem to get it to work correctly. My sample data set is below (The table name is FACT). My Formula is 

LY Units = CALCULATE(SUM('FACT'[UNITS]),SAMEPERIODLASTYEAR('FACT'[DATE])). Using this formula, I have been attempting to make a chart where you see the total units for 2020-Q1 and next to it the units for the same period last year. Currently when I do so the cell for same period last year is blank. I don't receive an error in the calculation but I don't get any values.

YEAR	QUARTER	MONTH	DATE	UNITS	CATEGORY
2020	Q1	1	Saturday, January 4, 2020	300	Juice
2020	Q1	1	Saturday, January 11, 2020	200	Juice
2020	Q1	1	Saturday, January 18, 2020	400	Juice
2020	Q1	1	Saturday, January 25, 2020	100	Juice
2019	Q1	1	Saturday, January 5, 2019	100	Juice
2019	Q1	1	Saturday, January 12, 2019	400	Juice
2019	Q1	1	Saturday, January 19, 2019	600	Juice
2019	Q1	1	Saturday, January 26, 2019	200	Juice
2020	Q1	1	Saturday, January 4, 2020	3000	Soda
2019	Q1	1	Saturday, January 5, 2019	2000	Soda

[V-lianl-msft · Accepted solution]

Hi @BH22One ,

After my test, the date in the data you provided is different every year, which is the reason for the blank value. But the calculation performs well in the bar chart.

Or you can customize time intelligence functions like this:

Result =
VAR Previous_Year =
    DATE ( YEAR ( MAX ( 'Table'[DATE] ) ) - 1, MONTH ( MAX ( 'Table'[DATE] ) ), DAY ( MAX ( 'Table'[DATE] ) ) + 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[UNITS] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CATEGORY] ),
            'Table'[DATE] = Previous_Year
        )
    )

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BH22One]

@V-lianl-msft Thanks for your input. This solution works perfectly in my report. I did however make a few tweaks to get it to work the way I anticipated. Instead of using a Max Date as a variable, I used the max year - 1 as a variable. This allows me to do a YoY comparison for multiple years.

[amitchandak]

@BH22One , Always date calendar in such case

Other options

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.