cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pratikshasingh2
Regular Visitor

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 @pratikshasingh2 

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 @pratikshasingh2 

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

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors