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

 

2 Replies

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