Need help

Hi,

I hope the below is what you are looking for.

I created a measure that gives The LATEST Rating result of the equipment among the current or from before until the current month. I also considered if one Equipment has two Ratings on the same day, then give the maximum result of Ratings, because "selectedvalue(Data[Rating])" gives blank value if it has two results.

test result (if it is not in this month, show last month result) =

VAR lasttestdate =

CALCULATE (

MAX ( Data[Test_Date] ),

FILTER ( ALL ( dates ), dates[Date] <= MAX ( dates[Date] ) )

)

VAR withresult =

CALCULATE ( SELECTEDVALUE ( Data[Rating], MAX(Data[Rating]) ), dates[Date] = lasttestdate )

RETURN

withresult

 

Then, I created the below measure.

 

equipment count by test result =

VAR newtable =

SUMMARIZE (

ALL(Equipments),

Equipments[Equipment],

"@testresult",

[test result (if it is not in this month, show last month result)]

)

VAR filtertable =

FILTER (

newtable,

[@testresult]

= SELECTEDVALUE ( Ratings[Rating] )

)

RETURN

IF (

ISBLANK ( [count of equipment] ),

BLANK (),

COUNTROWS ( filtertable )

)

 

 

 https://drive.google.com/file/d/17f_Q-xaoBpOodSLfT1WKq6g9y5Vyy585/view?usp=sharing