topic Re: Count rows with the latest date before a given date in DAX Commands and Tips

Count rows with the latest date before a given date

GRT9791 — Wed, 10 May 2023 09:53:04 GMT

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

Re: Count rows with the latest date before a given date

FreemanZ — Wed, 10 May 2023 10:29:05 GMT

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:

Re: Count rows with the latest date before a given date

GRT9791 — Mon, 22 May 2023 11:58:47 GMT

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