Return date when running total target is hit

[gyeea84]

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?

[v-lionel-msft · Accepted solution]

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.

[v-lionel-msft]

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]

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.  

[lbendlin]

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.