line graph - adding additional line based on same table data

[Anonymous]

I am currently graphing this data set and it allows me to filter for LE, RO, or LE + RO, however I would like to be able to to show my most recent submission LE AND my most recent submission LE + RO.  The graph I can currently get PowerBI to display is composed of the RED (Submission 4: LE + RO) and BLACK (Submission 3: LE ONLY).  I would like for the graph to also be able to display the BLUE line (Submission 4: LE ONLY).

Line Chart Settings:  Axis: Month; Values: Submission_Final; Values: Cumulative LERO

I have two slicers for the data - LE/RO and Submission 

These are the columns used from my dataset (Submission_Final is a measure):

Query: LE + R&O

LE/RO	Month	Variance ($k)	Submission_1	Submission_Final
RO	1	1	null	4
RO	1	2	null	4
RO	2	3	null	4
RO	3	4	null	4
RO	4	5	null	4
LE	1	-5	3	3
LE	1	6	2	2
LE	2	-3	3	3
LE	2	-5	4	4
LE	3	-4	3	3
LE	4	-7	2	2

Submission_Final = If( IsBlank('LE + R&O'[Submission_1]), MAX('LE + R&O'[Submission_1]),'LE + R&O'[Submission_1])

The lines in my graph are cumulative values - I used the following measure for them:

Cumulative LERO = CALCULATE (

SUM ('LE + R&O'[Variance]),

FILTER (

ALL ('LE + R&O'[Month]),

'LE + R&O'[Month] <= MAX ('LE + R&O'[Month])

)

)

Is it possible to create a measure to graph this data or how would you recommend I get this additional line added to my chart?  I do not need the LE + RO/LE to show for all the submissions, just the most recent.  

Thanks!

[v-yuta-msft]

@Anonymous ,

I am currently graphing this data set and it allows me to filter for LE, RO, or LE + RO, however I would like to be able to to show my most recent submission LE AND my most recent submission LE + RO.  The graph I can currently get PowerBI to display is composed of the RED (Submission 4: LE + RO) and BLACK (Submission 3: LE ONLY).  I would like for the graph to also be able to display the BLUE line (Submission 4: LE ONLY).

---

Could you clarify more about the logic to achieve "Submission 4"?

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Every month I get a new LE and RO (Risks & Opportunities) submission so basically Submission = Input Month and the LE (Latest Estimate) is spread out over multiple categories in the different months. 

I'm wondering if because I have Submission 1 and Submission_Final I can use my measure for Cumulative LERO and add a filter to use Submission 1 for one set of data (LE ONLY) and Submission_Final for the second set (LE + RO)...?  I would then remove the Legend and use Cumulative LERO and Cumulative LE.  However, I'm not sure how to formulat the filter to only use data that has a value for Submission 1 to calculate Cumulative LE.

Cumulative LERO would stay as it currently is and then Cumulative LE:

Cumulative LE = CALCULATE (
SUM ('LE + R&O'[Variance]),

DISTINCTCOUNT('LE + R&O'[Submission_1]),
FILTER (
ALL ('LE + R&O'[Month]),
'LE + R&O'[Month] <= MAX ('LE + R&O'[Month])
)
)

However when I try and use this measure I get the following error:  'The True/False expression does not specify a column...'

[Anonymous]

Okay, not sure if anyone can help out with this but I've attempted the following solution and I think it should work but I think my logic is incorrect.

I have removed my Slicer for LE/R&O & Submission with the intent that only the most recent Submission will be graphed on my line graph.  My line graph has the following measures input in the Values field:

(Blue Line - in diagram from first post)

Cumulative LERO = CALCULATE (

SUM ('LE + R&O'[Variance]),

FILTER (

ALL ('LE + R&O'[Month]),

'LE + R&O'[Month] <= MAX ('LE + R&O'[Month])

)

)

AND

(Red Line in diagram from first post)

Cumulative LE ONLY = CALCULATE(

CALCULATE (

SUM ('LE + R&O'[Variance]),

FILTER (

ALL ('LE + R&O'[Month]),

'LE + R&O'[Month] <= MAX ('LE + R&O'[Month])

&& MAX('LE + R&O'[Submission_1]))))

%Reasoning for this is that because Submission_Final has all the R&O values with a Submission_Final of 4 (Max submission) and Submission_1 has a null value for Submission_1 for the R&O values I should be able to see only the LE by filtering for Max Submission_1...

The only issue is that they're now showing the same values - which are incorrect and appear to be the cumulative value of ALL the submissions not just most the recent submission.