Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Columns for Cumulative balances

Hey there

 

I have a table set below for Date, Company, In.

 

May I know how can I create 2 columns that:

- calculates cumulative balances of "In" but only for individual company A, B & C

- calculates cumulative balances of "In" but for all companies

 

DateCompanyInCumu Bal by CompanyCumu Bal by Combined
2-JanA118
3-JanA1211
     
2-JanB118
3-JanB2311
4-JanB3614
     
2-JanC228
2-JanC468
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Anonymous

     

     

    you  need to build this model and then add these measures:

     

     

     

     

     

     

    Cumu Bal by Combined

     

    =IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE (
            MAX ( Data[Date] ),
            ALL ( 'Calendar' )
        ),
    CALCULATE (
        SUM ( Data[In] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        ALL ( Companies )
    )
)

    Cumu Bal by Company

     

    =IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE (
            MAX ( Data[Date] ),
            ALL ( 'Calendar' )
        ),
    CALCULATE (
        SUM ( Data[In] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)

     

1 Reply

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    Anonymous

     

     

    you  need to build this model and then add these measures:

     

     

     

     

     

     

    Cumu Bal by Combined

     

    =IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE (
            MAX ( Data[Date] ),
            ALL ( 'Calendar' )
        ),
    CALCULATE (
        SUM ( Data[In] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ),
        ALL ( Companies )
    )
)

    Cumu Bal by Company

     

    =IF (
    MIN ( 'Calendar'[Date] ) <= CALCULATE (
            MAX ( Data[Date] ),
            ALL ( 'Calendar' )
        ),
    CALCULATE (
        SUM ( Data[In] ),
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        )
    )
)