Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Table : PBI
I need to create an order Number input where people and put the value that how many orders they want so the salesperson can tell the first lead time date and the last lead time date. also, find the table and UI requirements below.
in the below example user will put order number requirement: 250 so needs to get first and last LT against particular series .
table PBI
SeriesCode | StartDate | TruckQuantityAvailable |
A1 | 18-01-2021 | 10 |
A1 | 20-01-2021 | 20 |
A1 | 14-06-2021 | 30 |
A1 | 26-10-2021 | 40 |
A1 | 21-12-2020 | 50 |
A1 | 23-12-2020 | 60 |
A1 | 28-10-2021 | 70 |
A1 | 10-06-2021 | 80 |
B1 | 18-01-2021 | 10 |
B2 | 20-01-2021 | 20 |
B3 | 14-06-2021 | 30 |
B4 | 26-10-2021 | 40 |
B5 | 21-12-2020 | 50 |
B6 | 23-12-2020 | 60 |
B7 | 28-10-2021 | 70 |
B8 | 10-06-2021 | 80 |
Output i need :
Order no ( Random series as parameter filter for input variable )
Inpur varibale as filter | No of orders | 250 |
First LT Date | Last LT date | |
Series Code | ||
A1 | 21-12-2020 | 14-06-2021 |
Solved! Go to Solution.
Hi @Anonymous
Try this.
1.
2.Create the two measures:
QTY_SUM_MEA =
VAR _selDate =
SELECTEDVALUE ( 'table PBI'[StartDate] )
VAR _selCode =
SELECTEDVALUE ( 'table PBI'[SeriesCode] )
RETURN
CALCULATE (
SUM ( 'table PBI'[TruckQuantityAvailable] ),
FILTER (
ALL ( 'table PBI' ),
'table PBI'[StartDate] <= _selDate
&& 'table PBI'[SeriesCode] = _selCode
)
)
Measure =
var _key=Parameter[Parameter Value]
var _FirstLTDate=MIN('table PBI'[StartDate])
var _LastLTDate=CALCULATE(MAX('table PBI'[StartDate]),FILTER('table PBI',[QTY_SUM_MEA]=_key))
var _SeriesCode = SELECTEDVALUE('table PBI'[SeriesCode])
return
IF(ISBLANK(_LastLTDate),"*Null*","[Series Code:"&_SeriesCode&"] [First LT Date:"&_FirstLTDate&"] [Last LT Date:"&_LastLTDate&"]")
Then,
Hope it helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Try this.
1.
2.Create the two measures:
QTY_SUM_MEA =
VAR _selDate =
SELECTEDVALUE ( 'table PBI'[StartDate] )
VAR _selCode =
SELECTEDVALUE ( 'table PBI'[SeriesCode] )
RETURN
CALCULATE (
SUM ( 'table PBI'[TruckQuantityAvailable] ),
FILTER (
ALL ( 'table PBI' ),
'table PBI'[StartDate] <= _selDate
&& 'table PBI'[SeriesCode] = _selCode
)
)
Measure =
var _key=Parameter[Parameter Value]
var _FirstLTDate=MIN('table PBI'[StartDate])
var _LastLTDate=CALCULATE(MAX('table PBI'[StartDate]),FILTER('table PBI',[QTY_SUM_MEA]=_key))
var _SeriesCode = SELECTEDVALUE('table PBI'[SeriesCode])
return
IF(ISBLANK(_LastLTDate),"*Null*","[Series Code:"&_SeriesCode&"] [First LT Date:"&_FirstLTDate&"] [Last LT Date:"&_LastLTDate&"]")
Then,
Hope it helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try with these three measures
measure = count(table[SeriesCode])
First LT Date = minx(values(Table[SeriesCode]) , if([measure]>=250, [SeriesCode], blank()))
Last LT Date = minx(values(Table[SeriesCode]) , if([measure]>=250, [SeriesCode], blank()))
I don't need count of series I need expected order no as input variable . For an example if I am going to dealer and saying I need 100 truck of this series so they will put 100 in input variable and sales person will tell me first and last date. ( First date will be when I can get 1 truc and last date is when I can get all 100 truck )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |