To calculate previous month of sales

[pmadam]

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.

[pmadam]

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

)

[Anonymous · Accepted solution]

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.

[tamerj1]

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 

[TomasAndersson]

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.

[tamerj1]

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
    )

[TomasAndersson]

Ah, a lot more sofisticated! Great suggestion @tamerj1 ! 

[TomasAndersson]

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

[tamerj1]

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
)