Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
KR,
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.
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
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.
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.
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
@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()
)
)
Proud to be a Super User! |
|
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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
21 | |
20 | |
20 | |
13 | |
13 |
User | Count |
---|---|
41 | |
28 | |
25 | |
23 | |
21 |