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/

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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors