Distinct Count with Date Slicer

[jbeckham812]

Hello All,

Complete newbie with Power BI/DAX here. Looking for a way to find out how many containers we had on the water at a given time headed to a certain location. I have a table called "Containers" that is layed out as follows:

CONTAINER #	Origin Departure Actual Date	Received	Destination
YMMU6066656	5/26/2021		Savannah
YMLU8615221	5/26/2021		Savannah
TGHU6078739	6/16/2021	7/22/2021	Perris

The count of containers would need to be based on the # of unique entries in the container # field. The amount of containers "on the water" at a given time would then be the # of unique container #'s with a departure date before the date that is chosen by using the date slicer, with a received date that is after the date chosen by using the date slicer. In the case of ongoing shipments, the received date would be blank, so therefore those would be included in the count only if their departure date was before the date chosen by the date slicer.  We would also want these counts separated based on the destination the shipment is going to.

So, in the example above, if the date slicer was on 6/20, it would show 2 containers on the water to savannah, and 1 to perris.

Please let me know if any more information is needed here, thanks in advance for the help!

[VahidDM]

Hi @jbeckham812 

Please use the below code: 

The count of containers = 
VAR _SelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date], TODAY () )
    // this VAR is to find the selected date by Slicer - if nothing selected that will return Today date
RETURN
    VAR _Count =
        CALCULATE (
            DISTINCTCOUNT ( 'Containers'[CONTAINER #] ),
            FILTER (
                FILTER ( 'Containers', 'Containers'[Origin Departure Actual Date] <= _SelectedDate ),
                OR ( ISBLANK ( 'Containers'[Received] ), 'Containers'[Received] > _SelectedDate )
            )
        )
    RETURN
        IF ( ISBLANK ( _Count ), 0, _Count )
        // this IF is for showingero when the value is Blank

The Output will be as follow:

** If you don't select any date on the Slicer, it will return the Today date. For more information please read about SELECTEDVALUE.

I added a calendar table to the model with the date column to use that in the slicer, but you can use any other columns with the date value and change "Calendar'[Date]" in line 3 of the above measure to the new column.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[jbeckham812]

@VahidDM ,

Thank you for your help. This measure works perfectly for calculating current # on the water, however it's not supplying the correct # when I choose past dates. I have a calendar table currently, with a relationship to a column in my container table called "Destination Arrival Actual Date".

It seems that when using the date slicer with the visual I created using your measure, it outputs the count of containers which have a "Destination Arrival Actual Date" that matches up to the date I've selected in the slicer. I've used USERELATIONSHIP on other measures to work around this on other visuals since my table contains many date fields, however on this one it appears that doing that ends up in the same issue, where the date slicer then causes the visual to give a count of containers that correspond with whichever date field I'm using.

Is there something that I'm missing here on this? 

[VahidDM · Accepted solution]

Hi @jbeckham812 

To remove the filter which made by relation between the Calendar Table and Containrs table, use the ALLEXCEPT code in the measure.

Please try this measure:

The count of containers =
VAR _SelectedDate =
    SELECTEDVALUE ( 'Calendar'[Date], TODAY () ) // this VAR is to find the selected date by Slicer - if nothing selected that will return Today date
RETURN
    VAR _Count =
        CALCULATE (
            DISTINCTCOUNT ( 'Containers'[CONTAINER #] ),
            FILTER (
                ALLEXCEPT ( Containers, Containers[Destination] ),
                'Containers'[Origin Departure Actual Date] <= _SelectedDate
                    && OR (
                        ISBLANK ( 'Containers'[Received] ),
                        'Containers'[Received] > _SelectedDate
                    )
            )
        )
    RETURN
        IF ( ISBLANK ( _Count ), 0, _Count )
// this IF is for showingero when the value is Blank

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[jbeckham812]

@VahidDM Works great, thank you so much for your help!