topic Re: Dax to populate next delivery date. in DAX Commands and Tips

Dax to populate next delivery date.

Polar_A — Wed, 10 Jul 2024 09:47:00 GMT

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,

Re: Dax to populate next delivery date.

bhanu_gautam — Wed, 10 Jul 2024 10:05:01 GMT

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()
)
)

Re: Dax to populate next delivery date.

Polar_A — Wed, 10 Jul 2024 10:09:19 GMT

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

Re: Dax to populate next delivery date.

Shaurya — Wed, 10 Jul 2024 11:56:01 GMT

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

Re: Dax to populate next delivery date.

Anonymous — Thu, 11 Jul 2024 02:17:20 GMT

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.

Re: Dax to populate next delivery date.

Polar_A — Thu, 11 Jul 2024 11:17:24 GMT

Hi Anonymous, 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

Re: Dax to populate next delivery date.

Anonymous — Fri, 12 Jul 2024 09:52:07 GMT

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.

Re: Dax to populate next delivery date.

Polar_A — Tue, 16 Jul 2024 11:49:51 GMT

Hi Anonymous,

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.