Return count of customer number and status based on date

[Anonymous]

I have gone around again and again and cannot solve this problem. I am trying to determine at a specific point in time what the status of a "Customer Number" is. For example, referencing the below table, on 1/1/2011 5835 would be "OPEN". If I move the date to 3/10/2011, it would be "CLOSED".

Customer Number	Transaction Date	Description	Number
5835	9/9/2010	OPEN	1
5835	1/20/2011	CLOSED	2
5835	4/28/2011	OPEN	3
5835	5/26/2011	CLOSED	4
5835	6/24/2011	OPEN	5
5835	8/11/2011	CLOSED	6
5835	9/1/2011	OPEN	7
5835	9/19/2011	CLOSED	8

I have thousands of these, and ultimately want to know that on X date, the count of open is Y and the count of closed is Z for all "Customer Numbers". 

I referenced this thread which is similar, but isn't getting me the results I want primarily because it's using MAX Date for all rows when I want to specify a specific point in time.

Any help is appreciated, I have people doing a bunch of work but they don't know how much they're doing or have been doing.

[v-lili6-msft]

HI, @Anonymous 

After my research, you could try this way as below:

Step1:

Create a separate date table to specify a specific point in time. 

Step2:

Create the two measure as below:

count of OPEN =
VAR _table =
    ADDCOLUMNS (
        'Table',
        "_lastdate", CALCULATE (
            MAX ( 'Table'[Transaction Date] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Customer Number] ),
                'Table'[Transaction Date] <= SELECTEDVALUE ( 'Date'[Date] )
            )
        )
    )
RETURN
    VAR _table2 =
        ADDCOLUMNS (
            _table,
            "Tag", IF ( [_lastdate] = [Transaction Date] && [Description] = "OPEN", 1, 0 )
        )
    RETURN
        SUMX ( _table2, [Tag] )

count of CLOSED = 
VAR _table =
    ADDCOLUMNS (
        'Table',
        "_lastdate", CALCULATE (
            MAX ( 'Table'[Transaction Date] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Customer Number] ),
                'Table'[Transaction Date] <= SELECTEDVALUE ( 'Date'[Date] )
            )
        )
    )
RETURN
    VAR _table2 =
        ADDCOLUMNS (
            _table,
            "Tag", IF ( [_lastdate] = [Transaction Date] && [Description] = "CLOSED", 1, 0 )
        )
    RETURN
        SUMX ( _table2, [Tag] )

Result:

Best Regards,

Lin

[Anonymous]

Sorry for the delay, I did not receive a notification.

This is very, very close.

For whatever reason, when I select the date it only shows open/closed for that date only. 

I've triple checked my math and formulas, but it's not aggregating all the customer numbers that are open and closed status for the given date.

[v-lili6-msft]

hi, @Anonymous 

I have tested on my sample pbix file, it works well.

If possible, please share your simple sample pbix file with your expected output for us have a test.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin