Forum Discussion

gyeea84's avatar
gyeea84
Regular Visitor
5 years ago
Solved

Return date when running total target is hit

Hi, 

 

Is it possible to return date when running total hit a specific target? F.ex. I would like to check the threshold within 2 weeks ahead and see if the target is below certain value. 

 

My dax which return running total looks something like this 

 

RunningTotal_DiffInOut =

var _runningTotal =
CALCULATE(
     [CurrentHBCapacity]+[Est_DiffInOut],
     FILTER(
         CALCULATETABLE(
             SUMMARIZE(
                 'NZ_VirtualTime',
                 'NZ_VirtualTime'[RelDagNr],
                 'NZ_VirtualTime'[OnlyDate_ForDisplay]
             ),
             ALLSELECTED('NZ_VirtualTime'), NZ_VirtualTime[OnlyDate_ForDisplay] >= format(today(),"yyyy.mm.dd")
         ),
         ISONORAFTER(
             'NZ_VirtualTime'[RelDagNr], MAX('NZ_VirtualTime'[RelDagNr]), DESC,
             'NZ_VirtualTime'[OnlyDate_ForDisplay], MAX('NZ_VirtualTime'[OnlyDate_ForDisplay]), DESC
         )
     )
)
 
return _runningTotal
 
How do I get the date if running tatal value falls below 1500 within the next 2 weeks?
 
Need Help
Show and Tell
Tips and Tricks
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi gyeea84 ,

     

    I know that your [current capacity], [total out] and [total in] are all measures, but I can't reproduce your model, so I can only use fact columns instead.
    Please refer to the formula below.

     

    Estimated remaining capacity = 
VAR sum_out = 
SUMX(
    FILTER( ALLSELECTED(Sheet2), [Date] <= MAX([Date]) ),
    [Total Out]
)
VAR sum_in = 
SUMX(
    FILTER( ALLSELECTED(Sheet2), [Date] <= MAX([Date]) ),
    [Total In]
)
RETURN
[current capacity] + sum_out - sum_in

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Create a measure where you have a table variable with all dates in your window, then calculate the running total for each of the dates, and then find the minimum date that has the running total above your threshold.

     

    Use variables along the way, and use CONCATENATEX() to verify that you get the expected intermediate results.

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi gyeea84 ,

     

    Please show us the sample data and the expected output result.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • gyeea84's avatar
      gyeea84
      Regular Visitor
      5 years ago

      Hi, 

       

       

      This is the example of the report. This report will estimate the available capacity in warehouse based on total pallet coming in and going out in daily basis. 

       

      Total in + out = data come from different tables and use measure to calculate

       

      Current capacity = data come from a table and use measure to calculate

       

      Estimated remaining capacity = current capacity + total out - total in. This is the running total measure because I need to accumulate the total based on daily basis. F.ex. today is 30.10, current capacity is 2066, total in is 841, total out is 1133, so estimated remaining capacity = 2066+1133-841=2358. The next day, 31.10, current capacity which is remaining capacity for today 2358, total in 55, total out 18, so 2358+18-55=2321, and calculation continue. The measure of this running total is on the original post. 

       

      My main goal is to return a date where the estimated remaining capacity hit, f.ex., 2000.  

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi gyeea84 ,

     

    I know that your [current capacity], [total out] and [total in] are all measures, but I can't reproduce your model, so I can only use fact columns instead.
    Please refer to the formula below.

     

    Estimated remaining capacity = 
VAR sum_out = 
SUMX(
    FILTER( ALLSELECTED(Sheet2), [Date] <= MAX([Date]) ),
    [Total Out]
)
VAR sum_in = 
SUMX(
    FILTER( ALLSELECTED(Sheet2), [Date] <= MAX([Date]) ),
    [Total In]
)
RETURN
[current capacity] + sum_out - sum_in

     

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.