Previous Month on Calculated Column

[Anonymous]

Hi everyone,

I have a table (named "table") with a large number of columns, but the related ones has the next structure:

id     date    import

1   201809     70

2   201809     88

1   201810     45

3   201811     36

1   201811     77

And i want to add a Calculated Column with the import of the previuos month. So the evolution for ID 1 will be:

      201809   201810   201811

1       NA           70           45

Do you have any idea? Of course i know i could create a measure like:

CALCULATE(SUM(import);PREVIOUSMONTH(table[date]))

But after this calculate, i want to create groups for numeric ranges about the differences between the import and the previous month import, and count how many IDs are in each group. I think i cant do this with a measure, so i want to have the previous on a calculated column.

I hope you can understand me. Thanks!

[Anonymous · Accepted solution]

Hi, You could create a calculated table with this measure: Table = SUMMARIZECOLUMNS(Table1[id]; "201809";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=08); "201810";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=09); "201811";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=10); "201812";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=11) )

[v-piga-msft · Accepted solution]

Hi @Anonymous,

By my tests, the formula from Chiara should be helpful.

You also could create an index calculated column and a measure to achieve that.

Index = 
RANKX (
    FILTER (
        'Table2',
        EARLIER ( 'Table2'[id] ) = 'Table2'[id]
    ),
    [date],
    ,
    ASC
)

Measure =
VAR a =
    CALCULATE (
        MAX ( Table2[import] ),
        FILTER (
            ALLEXCEPT ( 'Table2', Table2[id] ),
            'Table2'[Index]
                = MAX ( 'Table2'[Index] ) - 1
        )
    )
RETURN
    IF ( a = BLANK (), "Na", a )

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hi, You could create a calculated table with this measure: Table = SUMMARIZECOLUMNS(Table1[id]; "201809";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=08); "201810";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=09); "201811";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=10); "201812";CALCULATE(SUM(Table1[import]);MONTH('Date'[Date])=11) )