Skip to main content
cancel
Showing results for 
Search instead 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

Reply

Calculate previous month sales

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

Nerdywantocode_1-1701789329084.png

 

Nerdywantocode_2-1701789506939.png

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
Jamie_Scott
Resolver II
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())

Jamie_Scott_2-1701791095922.png

 

View solution in original post

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

 

Jamie_Scott_0-1701852141087.png

 

View solution in original post

7 REPLIES 7
Jamie_Scott
Resolver II
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())

Jamie_Scott_2-1701791095922.png

 

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

Nerdywantocode_0-1701831918679.png

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

Nerdywantocode_1-1701832011232.png

Could you help me resolve this problem.

Kind regards, 

 

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

 

Jamie_Scott_0-1701852141087.png

 

Thanks a lot. It works like charm

Thank you so much, it works now 😄

Jamie_Scott
Resolver II
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)

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

Nerdywantocode_0-1701790195457.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors