Forum Discussion

GRT9791's avatar
GRT9791
Frequent Visitor
3 years ago

Count rows with the latest date before a given date

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 ...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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's avatar
GRT9791
Frequent Visitor
3 years ago

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])