Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dalla_Terra
Helper I
Helper I

SUMX Date Range and SAMEPERIODLASTYEAR

This is prbably going to be an easy one but I am having a heck of a time doing something that seems like it would be very simple. 

 

The following is a calculation I use to calculate YTD sales when the users wants to see the data accumulated for the year; however, it is not so great when the user wants to see the data unaccumulated and the date range spans across years. 

 

Modifying things for the current period was no problem, I just altered the calculation to ignore YTD and it works jsut fine. 

Original Calculation: Sumx(Values(Dates[Year]),TOTALYTD(sum('Sales'[Price]),Dates[Date]))

Modified Version: Sumx(Values(Dates[Month/Year]),Sum('Sales'[Price]))

 

Now I need to show a calculation to display a value that represents the same time last year. I can't seem to use SAMEPERIODLASTYEAR because it seems to want the TOTALYTD function. I have also tried to use DATESINPERIOD with a CALCULATE function but the results are way off. 

 

CALCULATE(Sum('Sales'[Price]),DATESINPERIOD(Dates[Month/Year],LASTDATE(Dates[Month/Year]),-12,MONTH))

 

Is there a simple way to use the following so the results provided are for each row are the same time last year? 

 

Sumx(Values(Dates[Month/Year]),Sum('Sales'[Price]))

 

Below I am showing the Date as a row with the measure above as a column. I'd like a new colume with a measure for the same period last year. 

 

Screen Shot 2021-02-25 at 5.22.40 PM.png

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Dalla_Terra , It this moment I am not sure any value add "values" is doing(May be you want add the in grand total and this will do that.

Sumx(Values(Dates[Year]),TOTALYTD(sum('Sales'[Price]),Dates[Date]))

 

so you can try for last year and check is that can work

Sumx(Values(Dates[Year]),TOTALYTD(sum('Sales'[Price]),dateadd(Dates[Date],-1,year)))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

View solution in original post

3 REPLIES 3
Dalla_Terra
Helper I
Helper I

After taking a break and coming back to things the answer was very simple so I am adding it in the event others have a similar issue. 

 

My current period formula, which is driven by a date slider to select the range is: Sumx(Values(Dates[Year]),TOTALMTD(Sum('Sales'[Price]),Dates[Date]))

 

For this same range in the previous period I only needed to add a filter to my date in the TOTALMTD function so that it reads as follows: 

Sumx(Values(Dates[Year]),TOTALMTD(Sum('Sales'[Price]),SAMEPERIODLASTYEAR(Dates[Date])))

 

Hi @Dalla_Terra ,

Thanks for sharing your solution. Could you please mark your post as Answered? It will help the others in the community find the solution easily when they face the same problem with you. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@Dalla_Terra , It this moment I am not sure any value add "values" is doing(May be you want add the in grand total and this will do that.

Sumx(Values(Dates[Year]),TOTALYTD(sum('Sales'[Price]),Dates[Date]))

 

so you can try for last year and check is that can work

Sumx(Values(Dates[Year]),TOTALYTD(sum('Sales'[Price]),dateadd(Dates[Date],-1,year)))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.