Forum Discussion
I need to create input variable as filter and need Start Date and End date basis cumm reduction
I have future data of trucks of a particular series(ex: A099) getting manufactured date wise, I have existing order with me for which I need to calculate start date and end date of that order delivery...Now if a user enters a new order then the system should show a new start date and last date considering the existing order and new order.
Sample attched for your reference. for exiting order of 31 trucks I have start date = 26-10-2021 and last date = 02-11-2021, then for a new order of 100 trucks my start date should be 02-11-2021 and last date = 09-11-2021.
| Existing Order = 31 | New Order = 100 | |||||||
| 31 | 100 | |||||||
| SeriesCode | Date | No of Trucks getting Mnufactured | Orders Left | New no of Trucks getting manufactured | Orders Left | |||
| A099 | 26-10-2021 00:00 | 1 | 30 | First Date for Existing Order | 0 | 100 | ||
| A099 | 27-10-2021 00:00 | 7 | 23 | 0 | 100 | |||
| A099 | 28-10-2021 00:00 | 9 | 14 | 0 | 100 | |||
| A099 | 01-11-2021 00:00 | 13 | 1 | 0 | 100 | |||
| A099 | 02-11-2021 00:00 | 16 | -15 | Last Date for Existing Order | 15 | 85 | First Date for Delivery of New Order | |
| A099 | 03-11-2021 00:00 | 20 | -35 | 20 | 65 | |||
| A099 | 04-11-2021 00:00 | 23 | -58 | 23 | 42 | |||
| A099 | 08-11-2021 00:00 | 28 | -86 | 28 | 14 | |||
| A099 | 09-11-2021 00:00 | 33 | -119 | 33 | -19 | Final Date for Order Completion | ||
| A099 | 10-11-2021 00:00 | 38 | 38 | -57 | ||||
| A099 | 11-11-2021 00:00 | 42 | 42 | -99 | ||||
| A099 | 15-11-2021 00:00 | 46 | 46 | -145 | ||||
| A099 | 16-11-2021 00:00 | 50 | 50 | -195 | ||||
| A099 | 17-11-2021 00:00 | 55 | 55 | -250 | ||||
| A099 | 18-11-2021 00:00 | 57 | 57 | -307 |
Hi aditya143p ,
Create 2 columns as below:
Mark for quantity 30 = var _mindate=MINX('Table',[Date]) var _mindate1=MINX(FILTER('Table','Table'[Orders Left]<0),'Table'[Date]) Return IF('Table'[Date]=_mindate,"First Date for Existing Order",IF('Table'[Orders Left]<0&&'Table'[Date]=_mindate1,"Last Date for Existing Order",BLANK()) )Mark for quantity 100 = var _mindate1=MINX(FILTER('Table','Table'[Orders Left.1]<100),'Table'[Date]) var _mindate2=MINX(FILTER('Table','Table'[Orders Left.1]<0),'Table'[Date]) Return IF('Table'[Date]=_mindate1,"First Date for Delivery of New Order",IF('Table'[Date]=_mindate2," Final Date for Order Completion",BLANK()) )And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
Image also for output required
v-kelly-msftCommunity Support
Hi aditya143p
Which columns do you wanna get?The start and end date?Or the orders left?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi @Kelly
I need start and end date as output, rest all calculations can happen in background.
Thanks in advance.
- v-kelly-msft
Hi aditya143p ,
Create 2 columns as below:
Mark for quantity 30 = var _mindate=MINX('Table',[Date]) var _mindate1=MINX(FILTER('Table','Table'[Orders Left]<0),'Table'[Date]) Return IF('Table'[Date]=_mindate,"First Date for Existing Order",IF('Table'[Orders Left]<0&&'Table'[Date]=_mindate1,"Last Date for Existing Order",BLANK()) )Mark for quantity 100 = var _mindate1=MINX(FILTER('Table','Table'[Orders Left.1]<100),'Table'[Date]) var _mindate2=MINX(FILTER('Table','Table'[Orders Left.1]<0),'Table'[Date]) Return IF('Table'[Date]=_mindate1,"First Date for Delivery of New Order",IF('Table'[Date]=_mindate2," Final Date for Order Completion",BLANK()) )And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
