Total problem with SAMEPERIODLASTYEAR

[Anonymous]

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 (SUMX)] - [Total sales last year]

)

This is almost ok...  it work when I filter the on year 2020. But without the filter the total diff sales is wrong. Should be 1725, mot 2020.

Measue:

Total sales last year =

CALCULATE(

    [Total sales (SUMX)];

    ALL('Table');

    SAMEPERIODLASTYEAR('Table'[Date])

)

Measure:
Total sales (SUMX) =

SUMX(
    'Table';
    'Table'[Amout] * 'Table'[Price]

)

I can get it to work without filter , but then it is not working with the filter... And I am looking for a solution that works with and without filter.  😃

Pelase help!

[v-easonf-msft]

Hi, @Anonymous 

Maybe you only need to change your formula as below(need to build relationships  between tables first):

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

It will show as below:

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.
 

[Anonymous]

Thanks , but this is still not right. 
Without filter the column Diff sales between years is :
1515+15970+2320 = 20280 ... it should be 19805

But it works if I apply the year filter = 2020. 

20280 = total sales (2019+2020) - Total sales last year.

But it should only show diff between years.

I guess it is not possible to do it like this.

Thanks , i did not link the calendar to the data bable!

[v-easonf-msft · Accepted solution]

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.

[Anonymous]

Thanks!

This is working!

But I will need some time to really understand how it works. 🤗

(I have only been working with PBI for few months.)

[amitchandak]

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/

[Anonymous]

Sorry I can't get it to work. I am new to DAX (but been working a lot with Excel in the past)
I created a caledar : 

Calendar = CALENDARAUTO()

and change :

Total sales last year =

CALCULATE([Total sales (SUMX)]; SAMEPERIODLASTYEAR('Calendar'[Date])

)

Then I get the result : 
 
Date in the table picture is  the date from the data table. Not Calendar Date. 

[Anonymous]

Please learn the correct data modeling techniques:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

And remember: Power BI is not Excel. DAX is simple but it's not easy.

Best
D