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 cl...
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,

     

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?