Forum Discussion
Helper IIIMatrix for Dates after Latest Date
I have two tables, one for failures and one for repairs.
They're not related to each other, but they're both related to AllAssets, a list of all unique assets.
I want to create a matrix that shows all the failure dates after the latest repair date. I can make one that shows the latest repair date and all the failure dates by putting AllAssets and FailureDate in Rows and Latest RepairDate in Values, but I only want to show the FailureDates that appear after Latest RepairDate. Something like this:
Not really sure where to start with this one! Any help appreciated.
ReadTheIron You could use a measure as a filter (visual fitler) on the table as below. I have attached the file as an example to get you started
FailureDateAfterRepairDate = VAR MaxRepairDate = CALCULATE ( MAX ( Repair[RepairDate] ), ALLEXCEPT ( Repair, AllAssets[Asset] ) ) VAR CurrentFailureDate = SELECTEDVALUE ( Failure[FailureDate] ) RETURN IF ( CurrentFailureDate > MaxRepairDate, 1, BLANK () )
2 Replies
- moizsherwani
Continued Contributor
ReadTheIron You could use a measure as a filter (visual fitler) on the table as below. I have attached the file as an example to get you started
FailureDateAfterRepairDate = VAR MaxRepairDate = CALCULATE ( MAX ( Repair[RepairDate] ), ALLEXCEPT ( Repair, AllAssets[Asset] ) ) VAR CurrentFailureDate = SELECTEDVALUE ( Failure[FailureDate] ) RETURN IF ( CurrentFailureDate > MaxRepairDate, 1, BLANK () ) 
ReadTheIronThanks, this put me right on track! Do you know if there's any way to display the Latest RepairDate as a row in a matrix visual? I can add it as a value, but then it repeats in every line.
I've tried writing a measure for the max repair date, but when I include it on the row level it breaks the visual.