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 ...
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.

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.