cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION
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

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

Proud to be a Super User!

3 REPLIES 3
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])))

Community Support

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.
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

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

Proud to be a Super User!

Announcements