Forum Discussion

BH22One's avatar
BH22One
Icon for Helper I rankHelper 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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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.