Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Total problem with SAMEPERIODLASTYEAR

I have been struggling for some time now with this small&easy(?) problem..  ☹️  Measure: Diff sales between years = IF (     ISBLANK([Total sales last year]);     BLANK();     [Total sales (SU...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous

    Not very clear .

    Maybe what you need is a formula like this：

     

    2Diff sales between years = SUMX(DISTINCT('Date'[Date]),
IF (
    ISBLANK([Total sales last year]),
    BLANK(),
    [Total sales] - [Total sales last year]
))

    Here is a demo.

    Pbix attached

    Best Regards,
    Community Support Team _ Eason
    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
Super User
6 years ago

All-time intelligence functions need an end date to create a period. So if you do not provide then it will be based on table/calendar.

 

Better you create a calendar table and use this.

Total sales last year =
CALCULATE(  [Total sales (SUMX)];  SAMEPERIODLASTYEAR('Date'[Date])
)

Or 
Total sales last year =
CALCULATE(  [Total sales (SUMX)];  datediff('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/