Calculated measure with filter

[MarcDT21]

Hi there! 
I would like to answear the qestion, "how many kilometers have been driven since the last inspection?" Was the inspection (KontrOelAdblueetc) made then it's TRUE. So I somehow need to get to the last TRUE and sum up the distance (Gefahrene Distanz) or subtract the last KM of FALSE to the KM  of TRUE. How to I get all this in a measure?

Maybe @Anonymous you could help me out with that one as well... ? 🙂

[MarcDT21]

@v-jingzhang thank you very very much!!!! this measure is going to be one of the most important KPI in the safety of our company fleet/drivers!

[Syndicate_Admin]

Hi @MarcDT21 

You can try these measures

Measure = 
VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Datum]>last_true_date),'Table'[GefahreneDistanz])

or

Measure 2 = 
VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum])
VAR last_true_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=last_true_date),'Table'[KMEnde])
VAR last_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=MAX('Table'[Datum])),[KMEnde])
RETURN
last_date_KM - last_true_date_KM

Let me know if you have any questions. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[v-jingzhang]

Hi @MarcDT21 

You can try these measures

Measure = 
VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Datum]>last_true_date),'Table'[GefahreneDistanz])

or

Measure 2 = 
VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum])
VAR last_true_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=last_true_date),'Table'[KMEnde])
VAR last_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=MAX('Table'[Datum])),[KMEnde])
RETURN
last_date_KM - last_true_date_KM

Let me know if you have any questions. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[MarcDT21]

Dear @v-jingzhang 

Thank you very much for you two measures. The second measure is exactly what I was looking for. Unfortunately I still have troubles. 

When I try out the measure with steps then (e.g. first give me back the "last_true_date" then it will give me for all cars the last date of the last true - instead of each and every car its own last true date. -> see first picture.

Thank you very much for helping me!

Results with measure2
results with measure 2

Measure with steps

last true datelast km when true date

[v-jingzhang · Accepted solution]

Hi @MarcDT21 

I didn't know what FZ column represents for so I ignored that in previous reply. Anyway, try this new measure 2:

Measure 2 = 
VAR current_FZ_table = FILTER(ALL('Table'),'Table'[FZ]=SELECTEDVALUE('Table'[FZ]))
VAR last_true_date = MAXX(FILTER(current_FZ_table,'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum])
VAR last_true_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_true_date),'Table'[KMEnde])
VAR last_date = MAXX(current_FZ_table,'Table'[Datum])
VAR last_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_date),[KMEnde])
RETURN
last_date_KM - last_true_date_KM

Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it.

[MarcDT21]

last_true_date = VAR _current=MAX(CO2DatenApp[Datum]) RETURN MAXX(FILTER(ALL(CO2DatenApp), CO2DatenApp[Datum] <_current&&[KontrOelAdblueect]),CO2DatenApp[Datum])

[wdx223_Daniel]

last_true_date=VAR _current=max(date[date]) RETURN maxx(filter(all(date[date]),date[date]<_current&&[KontrOelAdblueetc]),date[date])

[Syndicate_Admin]

dear @wdx223_Daniel I thought I used your querry as intended, but results tell me otherwise. It gives me an odd date back. Could you please help me showing what I did wrong?

[MarcDT21]

dear @wdx223_Daniel I thought I used your querry as intended, but results tell me otherwise. It gives me an odd date back. Could you please help me showing what I did wrong?