Snapshots at point in time

[NewbieJono]

Hello, i have a table with

Start Date, Case Ref, Completion Date.

if completrion date is blank the case is open, else there will be a date.

is there a method i can select a past date from a filter to check how many cases were open at that time. 

[v-angzheng-msft · Accepted solution]

Hi, @NewbieJono 

May I ask if your problem has been solved and if you have seen the solution proposed by amitchandak , I think he gives a very detailed solution. For your needs, I have extracted the important parts of his case here to help others better solve the problem. I hope this helps, and iIf it helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you.

Sample data:

Result:

Still Open =
CALCULATE (
    COUNTX (
        FILTER (
            'Table',
            'Table'[Start Date] <= MAX ( 'Date'[Date] )
                && (
                    ISBLANK ( 'Table'[End Date] )
                        || 'Table'[End Date] > MAX ( 'Date'[Date] )
                )
        ),
        ( 'Table'[ID] )
    ),
    CROSSFILTER ( 'Table'[Start Date], 'Date'[Date], NONE )
)

Please refer to the attachment below for details

Best Regards,
Community Support Team _ Zeon Zheng

[amitchandak]

@NewbieJono , similar use case dicussed here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Also, check if the attached file after signature can help.