Missing part of line : might need measure with PREVIOUSMONTH and IF to find value but not sure

[RichardJ]

Hi,

Looking at the following two charts, we can see the lower one has a gap between March and April in the area shown.

I'd like to show the missing element in the lower chart but unfortunately it isn't available within my source data.

 I've been trying to use an IF statement to determine if the PREVIOUSMONTH = 0 then use the value from the 'Spent' column as the start point for the line but am struggling to do so.

The source excel file and the pbix files can be found via this link.

It isn't possible to change the source data.

Anyone got any ideas or the best way to approach this one so that I can get the lower chart to look like the top chart (and still not show a line for the Jan/Feb months despite them being 0 also)?

Thanks,

Richard

[parry2k]

@RichardJ use following measure for remaining instead of direct column

Remaining Measure = SUM( 'Power_BI_Feed_Data Set B'[Remaining] ) + 0

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[RichardJ]

Thanks for taking the time to respond @parry2k but the measure you are suggesting shows the line as follows where i'd like the line to meet the top of the March column plus not show any line for the January and February months

[parry2k]

@RichardJ try this measure

Remaining Measure = 
VAR __minDate = 
CALCULATE ( 
    MIN ( 'Power_BI_Feed_Data Set B'[Date] ), 
    ALLSELECTED(), 
    'Power_BI_Feed_Data Set B'[Remaining] <> BLANK()  
) 
VAR __lastMonth = EOMONTH ( __minDate, -1 )
RETURN
SUM( 'Power_BI_Feed_Data Set B'[Remaining] ) + 
IF ( EOMONTH ( MAX ( 'Power_BI_Feed_Data Set B'[Date] ), 0 ) = __lastMonth, 0 )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[RichardJ]

So close. Nice approach and not one i'd thought of.  Thanks @parry2k .
Is there any way I can extract the value for the previous month(March) and add it to the zero value so that the line starts at the top of the bar? 

[parry2k · Accepted solution]

@RichardJ here you go

Remaining Measure = 
VAR __minDate = 
CALCULATE ( 
    MIN ( 'Power_BI_Feed_Data Set B'[Date] ), 
    ALLSELECTED(), 
    'Power_BI_Feed_Data Set B'[Remaining] <> BLANK()  
) 
VAR __lastMonth = EOMONTH ( __minDate, -1 )
VAR __lastMonthSpent = CALCULATE ( SUM ( 'Power_BI_Feed_Data Set B'[Spent] ), EOMONTH ( 'Power_BI_Feed_Data Set B'[Date], 0 ) = __lastMonth ) 
RETURN
SUM( 'Power_BI_Feed_Data Set B'[Remaining] ) + 
IF ( EOMONTH ( MAX ( 'Power_BI_Feed_Data Set B'[Date] ), 0 ) = __lastMonth, __lastMonthSpent )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[RichardJ]

Fixed. Thanks for the lesson @parry2k. Appreciate you taking the time.

[parry2k]

@RichardJ it will show something like this

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!