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

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

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Dalla_Terra
Frequent Visitor

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

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors