Month on Month Comparison based on another value

[jamesholland198]

Hi,

I have the below table.  I want to add a 4th column, showing what the Billing value was for the relevant SageRef for the previous month.

So for example for SageRef Example1, on the 1st December row with Billing value 165.24, I'd want to add next to it the value for the same SageRef from 1st November, so 158.42

Any help appreciated, thanks.

SageRef	Month	Billing
Example1	Aug-20	114.56
Example1	Sep-20	114.56
Example1	Oct-20	157.45
Example1	Nov-20	158.42
Example1	Dec-20	165.24
Example2	Aug-20	156.98
Example2	Sep-20	167.23
Example2	Oct-20	162.56
Example2	Nov-20	194.87
Example2	Dec-20	225.26
Example3	Aug-20	220.89
Example3	Sep-20	217.21
Example3	Oct-20	210.89
Example3	Nov-20	859.65
Example3	Dec-20	364.85

[Anonymous · Accepted solution]

Hi @jamesholland198 

I think your Month column is text type, you may need to build a Month table as below.

Unrelated Month Table:

Then add a YearMonth column in your sample table.

YearMonth =
VAR _MonthName =
    LEFT ( 'Table'[Month], 3 )
VAR _MonthNum =
    CALCULATE (
        SUM ( 'Month'[MonthNumber] ),
        FILTER ( 'Month', 'Month'[Month] = _MonthName )
    )
VAR _YearNum =
    FORMAT ( RIGHT ( 'Table'[Month], 2 ), "General Number" ) + 2000
RETURN
    _YearNum * 100 + _MonthNum

Build the billing in previous month column by YearMonth column.

Billing in Previous Month = 
VAR _PreviousMonth =
    MAXX (
        FILTER (
            'Table',
            'Table'[SageRef] = EARLIER ( 'Table'[SageRef] )
                && 'Table'[YearMonth] < EARLIER ( 'Table'[YearMonth] )
        ),
        'Table'[YearMonth]
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Billing] ),
        FILTER (
            'Table',
            'Table'[SageRef] = EARLIER ( 'Table'[SageRef] )
                && 'Table'[YearMonth] = _PreviousMonth
        )
    )

Result 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. 

[jamesholland198]

Perfect, thanks very much

[amitchandak]

@jamesholland198 , with help from date table you can have month behind measure

example measure

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.