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?

  • 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. 

      • 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,