Earn vs Plan Line Graph

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Thanks you are awesome!