cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ipray Frequent Visitor
Frequent Visitor

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Earn vs Plan Line Graph

HI, @ipray 

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:

3.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Earn vs Plan Line Graph

HI, @ipray 

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:

3.JPG

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ipray Frequent Visitor
Frequent Visitor

Re: Earn vs Plan Line Graph

Thanks you are awesome!