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