Forum Discussion

MWitkin's avatar
MWitkin
Frequent Visitor
10 years ago
Solved

Cumulative Line Formula

Hello. I need to draw a cumulative line across this graph for both columns represented as bars in this graph visual: Both columns are date columns and are on the same table, and the bars r...
Need Help
Tutorial Requests
  • Twan's avatar
    Twan
    10 years ago

    You should be able to use the formula that Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

     

     

    Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

    Cummulative Planned Line =
    CALCULATE (
        COUNTA ( 'Table1'[Planned] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
        )
    )

     

    Here is what I generated with your example data set.

     

     

MWitkin's avatar
MWitkin
Frequent Visitor
10 years ago

Here a sample of the data&colon;

 

 

The report I showed an image of has bars that count the number of dates in the "Planned" and "Actual" columns and show them in bars to show the difference in the two counts. The "StartOfMonth" column is used to group the bars together by each month in the 'Shared Axis' field of the BI report. All columns are in one table. 

 

 

Twan's avatar
Twan
Advocate IV
10 years ago

You should be able to use the formula that Greg_Deckler provided and just change the SUM to a COUNT of [Planned] or [Actual].

 

 

Cummulative Actual Line =
CALCULATE (
    COUNTA ( 'Table1'[Actual] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

Cummulative Planned Line =
CALCULATE (
    COUNTA ( 'Table1'[Planned] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[StartofMonth] <= MAX ( 'Table1'[StartofMonth] )
    )
)

 

Here is what I generated with your example data set.