Forum Discussion

danialsj's avatar
danialsj
Frequent Visitor
6 years ago
Solved

Using a cumulative column to create a month-wise column

Data:
This table has cumulative values for each 'Code' shown monthwise. Code and Month are unique in the data.

Code             BALANCE_DATE                       Cumulative

1001January-196849.31
1001February-196849.31
1001March-1920821.91
1001April-1937260.27
1001May-1937260.27
1001June-1937260.27
1002February-1931.05
1002March-1931.05
1002April-1931.05
1002May-19310.44
1002June-19310.44
1002July-19310.44
1002August-19465.66
1002September-19465.66

 

Required:

I want a column that would show the amount generated each month (current month - previous month). What is the DAX for this?

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi danialsj ,

     

    Please try using EARLIER() function.

    Column =
'Table'[Cumulative]
    - CALCULATE (
        MAX ( 'Table'[Cumulative] ),
        FILTER ( 'Table', 'Table'[Code] = EARLIER ( 'Table'[Code] ) ),
        PREVIOUSMONTH ( 'Table'[BALANCE_DATE] )
    )

    Result would be shown as below.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    hi danialsj 

    try a measure

    Measure = selectedvalue(Table1[Cumulative]) - calculate(max(Table1[Cumulative]);filter(all('Table1');'Table1'[Code]=selectedvalue(Table1[Code]));PREVIOUSMONTH(Table1[BALANCE_DATE]))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

    • danialsj's avatar
      danialsj
      Frequent Visitor
      6 years ago

      Is there a way to make it a custom column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi danialsj ,

         

        Please try using EARLIER() function.

        Column =
'Table'[Cumulative]
    - CALCULATE (
        MAX ( 'Table'[Cumulative] ),
        FILTER ( 'Table', 'Table'[Code] = EARLIER ( 'Table'[Code] ) ),
        PREVIOUSMONTH ( 'Table'[BALANCE_DATE] )
    )

        Result would be shown as below.

         

        Best Regards,

        Jay

        Community Support Team _ Jay Wang

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