Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

I need to create input variable as filter and need to et first and last order date

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

SeriesCodeStartDateTruckQuantityAvailable
A118-01-202110
A120-01-202120
A114-06-202130
A126-10-202140
A121-12-202050
A123-12-202060
A128-10-202170
A110-06-202180
B118-01-202110
B220-01-202120
B314-06-202130
B426-10-202140
B521-12-202050
B623-12-202060
B728-10-202170
B810-06-202180

 

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   
A121-12-202014-06-2021

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

Try this.

1.

v-xiaotang_0-1620810930002.png

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,

v-xiaotang_1-1620810930009.png

v-xiaotang_2-1620810930013.png

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.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Anonymous 

Try this.

1.

v-xiaotang_0-1620810930002.png

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,

v-xiaotang_1-1620810930009.png

v-xiaotang_2-1620810930013.png

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.

amitchandak
Super User
Super User

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

Anonymous
Not applicable

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 ) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.