cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tom_SWE
Frequent Visitor

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

)

pbi.JPG

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!
1 ACCEPTED SOLUTION

Hi , @Tom_SWE

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.

 

View solution in original post

7 REPLIES 7
v-easonf-msft
Community Support
Community Support

Hi, @Tom_SWE 

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:

63.png

 

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.
 

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!

 

Hi , @Tom_SWE

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.

 

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
Super User
Super User

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/



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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 : 
pbi.JPG 
Date in the table picture is  the date from the data table. Not Calendar Date. 
Anonymous
Not applicable

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.