Right measure for a matrix visual

[mostho]

I have a table FT with 3 columns:
- DateA

- DateB

- Val
I have a matrix visual with DateB as Column, DateA as Row and sum of Val as values. works so far.

Now i wanna add a date slicer (with some given Dates e.g, from a disconnected table with one column DateC) which should filter the records from FT such that DateA > DateC and DateB < DateC

Example for FT:

DateA	DateB	Val
20-01-2024	15-12-2023	100
24-12-2023	07-12-2023	200
25-01-2024	07-01-2024	150
13-02-2024	20-11-2023	180
23-12-2023	17-11-2023	190

If I would select 31-12-2023 in the slicer the result should be:

	20-11-2023	07-01-2024	Total
25-01-2024		150	150
13-02-2024	180		180
Total	180	150	330

Additional Info: DateA and DateB are not unique. The combination of DateA and DateB also does not have to be unique.

[Anonymous · Accepted solution]

Hi @mostho 

First of all, I need to confirm your requirements with you. According to the condition that DateA > DateC and DateB < DateC, the output image should not be 07-01-2024.

The following testing is for your reference.

Create a measure as follows

Measure = IF(SELECTEDVALUE('Table'[DateA]) > SELECTEDVALUE('DateC'[Date]) && SELECTEDVALUE('Table'[DateB]) < SELECTEDVALUE('DateC'[Date]), 1, 0)

Put the measure into the visual-level filters, set up show items when the value is 1.

Output:

Best Regards,
Yulia Xu

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

[mostho]

@Anonymousyes, youre right, i had the wrong record in the result.

thank you very much for the solution, i was very close but now i know, what my problem of understanding was.