Sum sales by PREVIOUSMONTH by day

[rax99]

I need to show the previous months sales broken down by day. So last month on this day total sales was X.

Problem is the DAX calculate function is agregating sales of the entire month together, which isnt what I want.

Left one is showing correct when drilled by month, Right one is incorrect when drilled into day ( should show the sales amount for the same day last month)

DAX calc used

PreviousMonthSales = CALCULATE(SUM(Sales[TotalRev]),PREVIOUSMONTH('Date'[Date]))

I couldn't find any similar question asked in the past, which I thought was quite odd. ANy Help is appreciated,

[edhans · Accepted solution]

Try using the DATEADD() function.

=CALCULATE(
	[Total Sales],
	DATEADD('Calendar'[Date],-1,MONTH)
	)

It won't aggregate the previous period.

The problem is if the previous month doesn't have that day, it will repeat the last day of the month. So, if you are using that measure for March 29, 30, and 31, they will all report Feb 28. You would need to add some IF() logic to it to mask that if it is an issue.