cancel
Showing results for
Did you mean:
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]

)

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
Community Support

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.

7 REPLIES 7
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:

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.

Frequent Visitor

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!

Community Support

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.

Frequent Visitor

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

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://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
Frequent Visitor

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
Not applicable
Please learn the correct data modeling techniques:

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

Announcements

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.

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!

2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors