Count rows with the latest date before a given date

[GRT9791]

Hello, I have a table in my data model like the table shown in the below image.

In my report I have created a slicer to introduce a date and I need to create several measures to count rows with the different status. Measure A =count the rows with status A where Date<=the date selected in the slicer, Measure C =count the rows with status C Date<=the date selected in the slicer, etc. But I have count only one row per group (the row with the lastest date <= the selected date).

For example, if I select 03/04/2023 I need to count only the yellow rows --> The values would be: Measure A=2;  Measure B=0; Measure P=1; Measure R=0

Thanks a lot

[FreemanZ]

hi @GRT9791 

Not sure if i fully get you, please try to

1) plot a slicer with a calculated table like:

Slicer = ALL(data[Date])

2) plot a table visual with group and status columns and a measure like:

Measure = 
COUNTROWS(
    FILTER(
        data,
        data[Date]<MAX(Slicer[Date])
    )
)+0

it worked like:

[GRT9791]

Thanks a lot but it's not exactly what I need, because I have to count only one row (the row with the lastest date <= the selected date). Finally I managed to do it, doing the next steps:

1. Create a  measure that calculates the lastest date <= the selected date in the slicer: 

Measure1=

VAR fechamax =

    MAX (Fecha[Date] )

RETURN

    CALCULATE (

        MAX ( TABLA[Date] ),

        FILTER ( ALLEXCEPT('TABLA',TABLA[GROUP] ), TABLA[DATE] <= fechamax )

    )

2. Create an indicator measure:

Measure2=IF([Measure1]=SELECTEDVALUE(TABLA[Date]),1,0)

3. Create the counter measure:

counter=SUMX(TABLA, [Measure2])