Forum Discussion
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])
)
Total sales (SUMX) =
'Table';
'Table'[Amout] * 'Table'[Price]
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.
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
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/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.- 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
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.
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 19805But 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 , 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.
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.
