Calculate previous month sales

[Nerdywantocode]

Hi guys, I'm currently want to calculate previous month sale. This is my data table

For some reasons, I dont want to convert the column month into date to use DAX like PREVIOUS or EARLIER. My idea is that to calculate based on the value of the most nearest number, but I dont know how to do this. Please help me to do this, or if you have any greater idea, please share for me.

Best regards,

Nerd

[Jamie_Scott · Accepted solution]

Didn't think about that. Can you not just convert the month to date with DAX in a new column like:

MonthToDateConverted = DATE(LEFT(SalesTable[Month], 4), RIGHT(SalesTable[Month], 2), 1)

Then use 

PreviousMonthSales =

 CALCULATE(SUM(SalesTable[SALES]), PREVIOUSMONTH(SalesTable[MonthToDateConverted]), REMOVEFILTERS())

[Jamie_Scott · Accepted solution]

Didn't think about that. Can you not just convert the month to date with DAX in a new column like:

MonthToDateConverted = DATE(LEFT(SalesTable[Month], 4), RIGHT(SalesTable[Month], 2), 1)

Then use 

PreviousMonthSales =

 CALCULATE(SUM(SalesTable[SALES]), PREVIOUSMONTH(SalesTable[MonthToDateConverted]), REMOVEFILTERS())

[Nerdywantocode]

Hi Jamie, I want to ask you one more question. If I expand my table with product name like this:

and I used your DAX, when I use matrix visual to breakdown amount of product sold, the value of previous month did not show amount of each product, instead the total amount of previous month of all products

Could you help me resolve this problem.

Kind regards, 

[Jamie_Scott · Accepted solution]

Hi there,

I think it's because of the REMOVEFILTERS() piece. Could you try changing the measure to use the ALLEXCEPT function;

CALCULATE(
SUM(SalesTable[SALES]),
PREVIOUSMONTH(SalesTable[MonthToDateConverted]),
ALLEXCEPT(SalesTable, SalesTable[Product])
)

[Nerdywantocode]

Thanks a lot. It works like charm

[Nerdywantocode]

Thank you so much, it works now 😄

[Jamie_Scott]

If you don't want to convert to date format... It looks like you're using a numerical value, which should always increase in value, so you can just take the max -1 from that value.

Sales PrevMo =
VAR PrevMonth = MAX('Table'[Month]) - 1
RETURN
CALCULATE(SUM('Table'[Sales]), 'Table'[Month] = PrevMonth)

[Nerdywantocode]

Nice idea, I tried your solution earlier, but the problem when year change, the calculation skip it. The result is like this  😄