Forum Discussion

BH22One's avatar
BH22One
Helper I
6 years ago
Solved

Trouble with SAMEPERIODLASTYEAR function

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.

 

YEARQUARTERMONTHDATEUNITSCATEGORY
2020Q11Saturday, January 4, 2020300Juice
2020Q11Saturday, January 11, 2020200Juice
2020Q11Saturday, January 18, 2020400Juice
2020Q11Saturday, January 25, 2020100Juice
2019Q11Saturday, January 5, 2019100Juice
2019Q11Saturday, January 12, 2019400Juice
2019Q11Saturday, January 19, 2019600Juice
2019Q11Saturday, January 26, 2019200Juice
2020Q11Saturday, January 4, 20203000Soda
2019Q11Saturday, January 5, 20192000Soda
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

3 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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's avatar
      BH22One
      Helper I
      6 years ago

      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's avatar
    amitchandak
    Super User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.