Forum Discussion

awitt's avatar
awitt
Helper III
6 years ago
Solved

Conditional Measure Based on Date

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. 

 

OrderItemIDCONDATEFINALDATE

1234001

1/1/20201/30/2020
12340021/1/20201/30/2020
12340031/4/20201/6/2020
12340041/4/20201/30/2020
12340051/9/20201/30/2020
12340061/9/20201/30/2020

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

7 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      awitt
      Helper III
      6 years ago

      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?