Forum Discussion
Earn vs Plan Line Graph
Hi there, i need to create a line chart that has a "Plan" line that begins where the "Earned" line ends. I currently have the following measures for "Earned" & "Plan"
Earned Line =
IF(MAX(fact_ManhourProgress[Date]) <= TODAY(),
CALCULATE(
SUM(fact_ManhourProgress[Value]),
FILTER(ALLSELECTED(fact_ManhourProgress),fact_ManhourProgress[Date] <= MAX(fact_ManhourProgress[Date])),
FILTER(ALLSELECTED(fact_ManhourProgress),fact_ManhourProgress[Type]="Current"),
FILTER(ALLSELECTED(fact_ManhourProgress),fact_ManhourProgress[Spreadsheet Field]="Actual Units")))
Plan Line =
IF(MAX(fact_ManhourProgress[Date]) >= TODAY(),
CALCULATE(
SUM(fact_ManhourProgress[Value]),
FILTER(ALLSELECTED(fact_ManhourProgress),fact_ManhourProgress[Date] <= MAX('fact_ManhourProgress'[Date])),
FILTER(ALLSELECTED(fact_ManhourProgress[Type]),fact_ManhourProgress[Type]="Current"),
FILTER(ALLSELECTED(fact_ManhourProgress),fact_ManhourProgress[Spreadsheet Field]="Remaining Units")))
I need the plan line to take the total from the earned line and begin on the plan date.
Any help is much appreciated
HI, Anonymous
After my research, you could try this formula as Plan measure
Plan Line = IF ( MAX ( fact_ManhourProgress[Date] ) >= TODAY (), CALCULATE ( SUM ( fact_ManhourProgress[Value] ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Date] <= MAX ( 'fact_ManhourProgress'[Date] ) && fact_ManhourProgress[Date] >= TODAY () ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Current" ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Spreadsheet Field] = "Remaining Units" ) ) + CALCULATE ( SUM ( fact_ManhourProgress[Value] ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Date] <= MAX ( fact_ManhourProgress[Date] ) && fact_ManhourProgress[Date] <= TODAY () ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Current" ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Spreadsheet Field] = "Actual Units" ) ) )For example:
In my sample example:
Best Regards,
Lin
2 Replies
HI, Anonymous
After my research, you could try this formula as Plan measure
Plan Line = IF ( MAX ( fact_ManhourProgress[Date] ) >= TODAY (), CALCULATE ( SUM ( fact_ManhourProgress[Value] ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Date] <= MAX ( 'fact_ManhourProgress'[Date] ) && fact_ManhourProgress[Date] >= TODAY () ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Current" ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Spreadsheet Field] = "Remaining Units" ) ) + CALCULATE ( SUM ( fact_ManhourProgress[Value] ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Date] <= MAX ( fact_ManhourProgress[Date] ) && fact_ManhourProgress[Date] <= TODAY () ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Type] = "Current" ), FILTER ( ALLSELECTED ( fact_ManhourProgress ), fact_ManhourProgress[Spreadsheet Field] = "Actual Units" ) ) )For example:
In my sample example:
Best Regards,
Lin