If either date field column then distinct count for that day

[gpl]

Hey guys hope yall can help

I have a table that has a pick up date, delivery date, and a unit number. I am trying to do a distinct count on the unit number by date if either the pickup date or delivery date falls on that day

IE Pickup 6/1/2020 Delivery 6/2/2020 Unit R100 I would want R100 to count for 6/1/2020 and again for 6/2/2020

Hopefully this makes sense! I really appreciate the help!

Thanks

[amitchandak]

@gpl , you need to create a common date table and join both dates with that. One join will active one will be inactive. You can make use of userelation to choose the join.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

In case you need both dates to work on different time ranges, you can use them as filters.

[mahoneypat · Accepted solution]

Please try this approach for your expression

Units with OR =
VAR __thisdate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table[Unit] ),
        FILTER (
            Table,
            Table[DeliveryDate] = __thisdate
                || Table[PickupDate] = __thisdate
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat