cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate previous month sales

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

2 ACCEPTED SOLUTIONS
Resolver II

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())

Resolver II

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

7 REPLIES 7
Resolver II

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())

Helper I

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,

Resolver II

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

Helper I

Thanks a lot. It works like charm

Helper I

Thank you so much, it works now 😄

Resolver II

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)

Helper I

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