Dax to populate next delivery date.

[Polar_A]

Hi I need a bit of help with one of my DAx measures.

I'm trying to generate the next delivery date to show after today.

My current measure is

Next Delivery Date =

CALCULATE(

    MIN('Delivery Schedule'[clu_deliverydate.1]),

    'Delivery Schedule'[clu_deliverydate.1] > TODAY(),

    ALLEXCEPT(Patients,Patients[Patient ID])

).

This removes all of the past delivery dates but I want to limit this one to only show the next one. For example this will show delivery 03 and only Delivery 03

KR,

[v-kaiyue-msft]

Hi @Polar_A ,

Thanks for the reply from @bhanu_gautam  and @Shaurya , please allow me to provide another insight: 

You can try this.

Next Delivery Date =
CALCULATE(
    MINX(
        TOPN(
            1,
            FILTER(
                'Delivery Schedule',
                'Delivery Schedule'[clu_deliverydate.1] > TODAY()
            ),
            'Delivery Schedule'[clu_deliverydate.1],
            ASC
        ),
        'Delivery Schedule'[clu_deliverydate.1]
    ),
    ALLEXCEPT(Patients, Patients[Patient ID])
)

If this doesn't solve your problem. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Polar_A]

Hi @v-kaiyue-msft, @Shaurya, @bhanu_gautam.

Thank you all this was the best solution for me with the measure NDD showing as Next Day Delivery.

I am now trying to group it by the prescription number, so that with prescription number 3001067 shows delivery 02 and not delivery 03; Prescription Number 3001034 shows Delivery 03 and not Delivery 04, 05, 06, etc..

Essentially I want to next delivery based on the prescription number.

KR

[v-kaiyue-msft]

Hi @Polar_A ,

Create measure.

Next Delivery Date =
VAR _date =
    CALCULATE (
        MIN ( 'Delivery Schedule'[clu_deliverydate.1] ),
        'Delivery Schedule'[clu_deliverydate.1] > TODAY (),
        FILTER (
            'Patients',
            'Patients'[Prescription] = MAX ( 'Patients'[Prescription] )
        )
    )
RETURN
    IF ( MAX ( 'Delivery Schedule'[clu_deliverydate.1] ) = _date, 1, 0 )

Then filter the data with Next Delivery Date of 1 in the filter.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Polar_A]

Hi @v-kaiyue-msft,

Thank you.

I tried this and it is still showing everything rather than just the one delivery which i would want to be delivery 04.

[Shaurya]

Hi @Polar_A,

Try using this:

Next Delivery Date = 
CALCULATE(
    MIN('Delivery Schedule'[clu_deliverydate.1]),
    FILTER(
        ALLEXCEPT(Patients, Patients[Patient ID]),
        'Delivery Schedule'[clu_deliverydate.1] > TODAY()
    )
)

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI  

[bhanu_gautam]

@Polar_A , Try using below measure

Next Delivery Date =
CALCULATE(
MIN('Delivery Schedule'[clu_deliverydate.1]),
FILTER(
ALLEXCEPT('Delivery Schedule', 'Delivery Schedule'[Patient ID]),
'Delivery Schedule'[clu_deliverydate.1] > TODAY()
)
)

[Polar_A]

Hi @bhanu_gautam,

Its not working as the Patients ID is in a different table. It is in the Patients Stable and not the delivery Schedule table.

KR