Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Month on Calculated Column

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    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's avatar
    v-piga-msft
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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