DAX code to calculate cumulative count of events

0
votes

I have a simple powerpivot model with a datetable and a milestone table.

enter image description here shown in the figure. The milestone table has columns of milestone forecast and actual dates, eventually for six major milestones but two are shown. I am able to summarize (calculated field) the COUNT of forecasted or actual milestone events by week and year. But I cannot see how to also generate a cumulative total (YTD) of these counts - probably missing something that should be obvious.

The two calculated fields that work are:

MS1 YTD Forecast:=CALCULATE(COUNT([Forecast MS1 Date]))

MS1 YTD Actual:=CALCULATE(COUNT([Actual MS1 Date]), USERELATIONSHIP(milestones[Actual MS1 Date],datetbl[Date]))

The pivotchart of this shows enter image description here

Eventually I would like to create a report showing the progress for each milestone (actual vs forecast) for this project.

1
powerpivotdax

1 Answers

0
votes

I have a partial answer. Added 'helper columns' with a 1 or 0 based on existence of a date. THEN used Javier Guillen's "Running Totals" post (https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/) to create the lines.

But I get a syntax error when I attempt to apply the DAX Cumulative pattern to FILTER for valid dates. My measure is: Cumulative MS1 Actual := IF ( MIN ( 'datetbl'[Date] ) <= CALCULATE ( MAX (milestones[Actual MS1 Date] ), ALL (milestones) ), CALCULATE ( SUM (milestones[MS1 Actual exists] ), USERELATIONSHIP(milestones[Actual MS1 Date],datetbl[Date]), FILTER ( ALL ( 'datetbl'[Date] ), 'milestones'[MS1 Actual Date] <= MAX ( 'milestones'[MS1 Actual Date] ) ) ) )
I am getting a semantic error 'column 'MS1 Actual Date' in table milestones cannot be found or may not be used in this expression.

unfortunately I cannot seem to post the updated chart or datamodel