WIP measure that counts on blanks

[Ravenholm]

Hello,

New to this so hoping i am posting in the correct area.

I have been playing about with building a measure but not sure if what I am doing is possibe or if I would be best approaching it from another direction.

I have a model that has two tables in it, one is calendar that has all dates in it from 1900 to 2099. The other is a data table that has 3 columns in it 'Referance', 'AppDate' and 'OutcomeDate' (I will be adding more but these are the important fields for now).

'Referance' is numerical, and all the Date fields are in the format of short date. The two tables are joined Many to One (many in the data to the one date in the calendar).

This is an example of how my data would exist inthe data table.

Referance	StartDate	OutcomeDate
1	02/02/1999	31/12/2000
2	02/02/1999	31/12/2000
3	02/02/1999	31/12/2000
4	02/02/1999	31/12/2000
5	02/02/1999	31/12/2000
6	02/02/1999
7	02/02/1999
8	02/02/1999
9	02/02/1999
10	02/02/1999
11	01/01/2023	28/01/2023
12	01/01/2023	28/01/2023
13	01/01/2023	28/01/2023
14	01/01/2023	28/01/2023
15	01/01/2023	28/01/2023
16	01/01/2023
17	01/01/2023
18	01/01/2023
19	01/01/2023
20	01/01/2023

I want a measure that i can use in the y-axis of a chart that will be able to do the following:

1. If there is no OutcomeDate then count it against all dates on and after its StartDate
2. If there is an OutcomeDate then count it againt all days on and after its StartDate but not on or after the OutcomeDate

The x-axis on the chart will be represented by the date from the linked calendar table.

So using the example data if i was looking at each day in the weeks starting:

06/03/2023 - every day should have a count of 10 for the open cases

01/01/2023 - every day should have a count of 15 for the open cases and those not closed until the 28/01/2023

28/11/2022 - every day should have a count of 5 for the open cases from 02/02/1999

11/10/1999 - every day should have a count of 10 for all the cases opened on 02/02/1999

thank you for your time, I hope i have provided enough information to go. Would love for this to be a measure, but if i need to add something to the data table to enable this that is also possible.

Thanks,

R

[Ravenholm]

Good evening @tamerj1 thank you so much for taking the time to respond.

I have put in to practice your suggestion but it does not yeild my desired result. what i am finding is that on my chart i will only have values returned against certain dates and not all date.

Between 02/02/1999 and 30/12/2000 it returns a value of 5 against all dates, we know from the data the date returned should be 10.

In then returns no values beween 31/12/2000 and 31/12/2022.

It then starts to return values again on 01/01/2023 until 27/01/2023 with the value of 5 which again from the data we know to be incorrect.

I would also if possible like to retaint he link before the two table if that is possible with what I want to achive.

Any further help would be much appriciated.

Thanks,

R

[tamerj1]

@Ravenholm 
Oh! I missed one detail. Sorry for that

Count =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[StartDate] <= CurrentDate
                && COALESCE ( 'Table'[OutcomeDate], DATE ( 2099, 12, 31 ) ) > CurrentDate
        )
    )

[Ravenholm]

@tamerj1 Good Morning and thank you agian.

I have tested the new measure out and it does not achive what i need.

However, I have made some adjustments to the original measure that you provided me with and have come up with this:

Count =
VAR CurrentDate =
    MAX('Date'[Date])
RETURN
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[StartDate] <= CurrentDate
                && (
                    ISBLANK('Table'[OutcomeDate])
                    || 'Table'[OutcomeDate] > CurrentDate
                )
        )
    )

This appears to be doing what i need. However, I feel that the data model will need to to have the relationship that I have removed. is there a way to make sure that is included?

thank you again for your time

[tamerj1]

Hi @Ravenholm 

please delete the relationship or deactivate it. Try thr following measure 

Count =
VAR CurrentDate =
MAX ( 'Date'[Date] )
RETURN
COUNTROWS (
FILTER (
'Table',
'Table'[StartDate] <= CurrentDate
&& 'Table'[OutcomeDate] > CurrentDate
)
)