Calculate Number of Rows before a Date, for every day

[Walt1010]

Noob here. How do you count the number of rows before a certain date, and be able to show this value for every day in the calendar?

I have tried the following, where I have an active (1:many) relationship betwen Date Table[Date] and Property Table[Vacated Date]:

Before Vacated Date Count = CALCULATE(

COUNTROWS(Property Table),

Property Table[Vacated Date]>

SELECTEDVALUE( 'Date Table'[Date])

)

This doesnt provide any results at all. If I remove the SELCTEDVALUE I get an error. Any suggestions would be helpful. Thanks.

I dont get any results at all.

[Anonymous · Accepted solution]

Hi @Walt1010 ,

I create two tables as you mentioned and I also build a relationship between them.

Then I create a measure and it will get what you want.

Before Vacated Date Count =
VAR _Slicer =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    COUNTROWS ( FILTER ( ALL ( 'Table' ), 'Table'[Vacated Date] > _Slicer ) )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Walt1010 maybe something like this, depends how you are viewing the data:

Orders Count = 
VAR __SelectedDate = MAX ( 'Calendar'[Date] ) 
RETURN
CALCULATE ( 
    COUNTROWS ( PropertyTable ),
    FILTER ( 
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= __SelectedDate
    )
)