cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rax99 Member
Member

Sum sales by PREVIOUSMONTH by day

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)

 

previousmonth.JPG

 

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,

1 ACCEPTED SOLUTION

Accepted Solutions
edhans New Contributor
New Contributor

Re: Sum sales by PREVIOUSMONTH by day

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.

1 REPLY 1
edhans New Contributor
New Contributor

Re: Sum sales by PREVIOUSMONTH by day

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.