Forum Discussion
Cumulative Line Formula
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.
Here a sample of the data:
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.
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.
I implemented the formulas suggested above, but the numbers illustrated don't seem to match up to the numbers on the bars.
You can see the line value at the highlighted point is 8,370 while the bars is hovers by are both only showing a value of 15. It seems to just be doubling it's count of every item in the data set (there are about 550 total items in the data set by their dates span over a long period of time).
Did I miss a step?
Judging by the image Twan showed of his solution, that formula should work perfectly.
