topic Re: To calculate previous month of sales in DAX Commands and Tips

To calculate previous month of sales

pmadam — Wed, 29 Jun 2022 06:02:15 GMT

I am using below formula to calculate previous month but i get black values for few months and other months are blank values as shpwn below, not sure whats the issue.

PM Book RR = Calculate(SUM('Sales vs Inv'[Book RR]),FILTER(ALL('_DimDate (2)'[MonthOfYear]),'_DimDate (2)'[MonthOfYear] = MAX('_DimDate (2)'[MonthOfYear])-1))

Kindly suggest.

Re: To calculate previous month of sales

tamerj1 — Wed, 29 Jun 2022 07:30:32 GMT

Hi pmadam
Please try

PM Book RR = CALCULATE ( SUM ( 'Sales vs Inv'[Book RR] ), REMOVEFILTERS ( '_DimDate (2)' ), '_DimDate (2)'[MonthOfYear] = MAX ( '_DimDate (2)'[MonthOfYear] ) - 1 )

Re: To calculate previous month of sales

TomasAndersson — Wed, 29 Jun 2022 08:03:51 GMT

Hi!

Not every month has a Book RR. So for month 11 there is a value to retrieve for month 11-1 = 10, but for 10 there is nothing for month number 10-1 = 9, and so on. The "-1" you add in your filter decreases the month number by exactly 1, not down to the one with one rank lower.

Maybe it makes sense to have previous month as 0/blank if there is data for October (10) but nothing for September (9)?

But maybe you want to see the sales for the most recent month where there has been a sale. I'm not sure how your data is structured, you'd have to share some for me to be more specific. I suppose you could create a summarized table with the numbers you show here, create a calculated column with the rank for each month number, and then use this rank in your filter to get the most recent month for each. Another way could be to nest if statements: if sales for month-1 is blank, pick sales for month-2, and so on.

But there might be an easier solution somehow.

Re: To calculate previous month of sales

TomasAndersson — Wed, 29 Jun 2022 08:23:48 GMT

EDIT: See tamerj1s post for a smarter solution.

See below for example with nested ifs. It's not pretty, but it works.

PM Book RR = VAR __monthsToDecrease = if( CALCULATE ( SUM ( 'Sales vs Inv'[Book RR] ), FILTER( ALL(''_DimDate (2)'[MonthOfYear]),'_DimDate (2)'[MonthOfYear] = MAX('_DimDate (2)'[MonthOfYear])-1) ) > 0,1,2) //nest additional times to include possibility that __monthsToDecrease might have to be 3, 4 etc... return CALCULATE ( SUM ( 'Sales vs Inv'[Book RR] ), FILTER( ALL('_DimDate (2)'[MonthOfYear]),'_DimDate (2)'[MonthOfYear] = MAX('_DimDate (2)'[MonthOfYear])-__monthsToDecrease) )

Re: To calculate previous month of sales

tamerj1 — Wed, 29 Jun 2022 08:19:21 GMT

Hi TomasAndersson
Please use

PM Book RR = VAR CurrentMonthOfYear = MAX ( '_DimDate (2)'[MonthOfYear] ) VAR PreviousMonth = MAXX ( FILTER ( ALL ( '_DimDate (2)'[MonthOfYear] ), '_DimDate (2)'[MonthOfYear] < CurrentMonthOfYear ), '_DimDate (2)'[MonthOfYear] ) RETURN CALCULATE ( SUM ( 'Sales vs Inv'[Book RR] ), REMOVEFILTERS ( '_DimDate (2)' ), '_DimDate (2)'[MonthOfYear] = PreviousMonth )

Re: To calculate previous month of sales

TomasAndersson — Wed, 29 Jun 2022 08:23:09 GMT

Ah, a lot more sofisticated! Great suggestion tamerj1 !

Re: To calculate previous month of sales

pmadam — Wed, 29 Jun 2022 19:38:46 GMT

Hi tamerj1 TomasAndersson ,

Thanks for your help.

I got it working for multiple solutions but its now showing previous month for January of each year.

Please suggest where I wrong

PM Book RR = Calculate(SUM('Sales vs Inv'[Book RR]),FILTER(ALL('Sales vs Inv'[Time.Month]), 'Sales vs Inv'[Time.Month] = MAX('Sales vs Inv'[Time.Month])-1))

PM Book RR1 =

CALCULATE (

SUM ( 'Sales vs Inv'[Book RR] ),

removeFILTERS ('Sales vs Inv'[Time.Month]),

'Sales vs Inv'[Time.Month]

= MAX ( 'Sales vs Inv'[Time.Month] ) - 1

)

Re: To calculate previous month of sales

tamerj1 — Wed, 29 Jun 2022 21:18:55 GMT

Hi pmadam

you have to have a year month sequential number column. If you already have year month number in the format YYYYMM then you can RANKX - ASC - DENSE based on this column. Then use this column in the measure

Re: To calculate previous month of sales

Anonymous — Tue, 05 Jul 2022 09:23:23 GMT

Hi pmadam ,

According to your statement, I suggest you to combine [Year] and [Time.Month] into one column as a key column in your calculation. Using two columns [Year] and [Time.Month] will make your calculation complex. Thiis new column should be in number type. Note: [Year] and [Time.Month] should be in number type as well.

YearMonth = [Year]*100 + [Time.Month]

Measure:

PM Book RR1 = VAR _PREVIOUS_YEARMONTH = MAXX ( FILTER ( ALL ( 'Sales vs Inv' ), 'Sales vs Inv'[YearMonth] < MAX ( 'Sales vs Inv'[YearMonth] ) ), 'Sales vs Inv'[YearMonth] ) RETURN CALCULATE ( SUM ( 'Sales vs Inv'[Book RR] ), FILTER ( ALL ( 'Sales vs Inv' ), 'Sales vs Inv'[YearMonth] = _PREVIOUS_YEARMONTH ) )

Result in my sample is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.