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 - cal...
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] )
        )
    )
)

     

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] )
        )
    )
)