Conditional Measure Based on Date

[awitt]

I have a set of data that has one record per OrderItemId which is a specific unit of a sale. I have two columns, CONDATE which is the begging date of this sale and FINALDATE which is when the sale closed in some way. I also have a separate date table.

What I want is to do is have a date field that a user enters and a measure returns how many OrderItemIDs were "active" for that specific date. 

For example - if the date selected by the user was 1/3/2020, the result would be 2 since orders 1234001 & 1234002 had been ordered but not yet finalized. If the date was 1/5/2020 then the result would be 4. If the date was 1/8/2020 the result would be 3 since OrderItemID 1234003 had hit a FINALDATE of 1/6, so this order would be excluded. 

OrderItemID	CONDATE	FINALDATE
1234001	1/1/2020	1/30/2020
1234002	1/1/2020	1/30/2020
1234003	1/4/2020	1/6/2020
1234004	1/4/2020	1/30/2020
1234005	1/9/2020	1/30/2020
1234006	1/9/2020	1/30/2020

[v-lid-msft]

Hi @awitt ,

We can create a calculated table and a measure to meet your requirement:

Calculated table:

DateSlicer = CALENDAR(DATE(2019,1,1),DATE(2021,12,31))

Measure:

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[OrderItemID] ),
    FILTER (
        'Table',
        NOT (
            OR (
                'Table'[CONDATE] > MAX ( 'DateSlicer'[Date] ),
                'Table'[FINALDATE] <= MIN ( 'DateSlicer'[Date] )
            )
        )
    )
)

By the way, PBIX file as attached.

Best regards,

[awitt]

@v-lid-msft I'm pretty sure this is going to work for my dataset. The hard thing is proving the results. Is there a way to have a table that only returns the results of a given day as well?

For instance, I have some days where the result for that day is over 20,000. I know i'm going to be asked which 20,000 records made up that number but I haven't been able to do that yet. Thoughts?

[v-lid-msft]

Hi @awitt ,

We can put the measure into visual filter of table chart, then set it "is not blank" or "is not zero" to meet your requirement:

By the way, PBIX file as attached.

Best regards,

[awitt]

@v-lid-msft The only thing that seems off is that Blank FINALDATE values are not included in the measure and they should be. If this is essentially open orders, there wont be Final Dates for active orders that haven't yet been completed. 

[v-lid-msft]

Hi @awitt ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @awitt ,

We can try to use the following measure to meet your requirement:

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[OrderItemID] ),
    FILTER (
        'Table',
        OR (
            ISBLANK ( 'Table'[FINALDATE] ),
            NOT (
                OR (
                    'Table'[CONDATE] > MAX ( 'DateSlicer'[Date] ),
                    'Table'[FINALDATE] <= MIN ( 'DateSlicer'[Date] )
                )
            )
        )
    )
)

Best regards,

[Greg_Deckler]

You will need something like Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364