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,

     

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. 

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    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 ,

     

    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,