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

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.