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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.