Comparison of Current month's sales and previous nonblank month's sales

[KRYSWT]

Hi all,

I need to compare item based current month's sales and previous month's sales. I used the below formula and works fine if any sales in the previous month. 

                Prev_Month_Sales =  CALCULATE(SUM(SALES[SalesAmount])); DATEADD('DATE'[Date]; -1 ; MONTH))

But if there is no sales in previous month i want to compare the current month's sales and the previous nonblank month's sales 

	Nov.17	Dec.17	Jan.18	Feb.18	Mar.18	Apr.18	May.18
Item	1000			1100	1500		2000

For example for Feb.18 i need to compare Feb.18 sales (1100) and Nov.17 sales (1000) because there are no sales in previous two months.

Many thanks.

[v-danhe-msft]

Hi @KRYSWT

could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @KRYSWT,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem.

2.Create two calculated columns. Now you can see the result.

last Amount = var temp='Table1'[Date]

return

CALCULATE(LASTNONBLANK('Table1'[Amount],'Table1'[Amount]),ALL('Table1'),'Table1'[Date]<temp)

Change = IF(ISBLANK([Amount]),BLANK(),[Amount]-[last Amount])

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/pfm5i0f36tm1rqy/Comparison%20of%20Current%20month%27s%20sales%20and%20prev...

Regards,

Daniel He