Help convert a table into a chart

[Penn]

Hi all,

I have simplied my question and upload an example file here: 

https://drive.google.com/file/d/1sLSgz5iNBRoj07_yMIkgzpuXBxwfPVCk/view?usp=sharing

The raw data looks like this.

Case Table

Date Table

What I am trying to do here is to find out at the end of each month, what is the latest status the case is at. And I have successfully create a measure and show the result in a table.

But when I tried to convert the table into a line chart, things become funny. Why does the line Count of Disqulifed Case still there after 31/10/2019? What I am expecting to see if only disqualifed line between 30/06/2019 to 30/09/2019 and only converted line after 31/10/2019 as per the table.

[v-juanli-msft · Accepted solution]

Hi @Penn

It is ok?

Creating Measures

fill status = CALCULATE(FIRSTNONBLANK('Case'[EOM Status],1),FILTER(ALLSELECTED('Case'),'Case'[EOM Date]<=MAX('Case'[EOM Date])))

Disqulifed = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Disqualified"))

converted = CALCULATE(DISTINCTCOUNT('Case'[Case ID]),FILTER(ALLSELECTED('Case'[Case ID]),'Case'[fill status]="Converted"))

the 'date2' is a table created by me and has a relationship with the main table

date2 = CALENDARAUTO()

Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, please consider Accepting it as the solution to help other members find it more quickly.

[Penn]

Thanks Maggie, works fine with the example, will integrate into my report.

[Penn]

all measures can be found in the attached file