Forum Discussion

jaymccorp's avatar
jaymccorp
Frequent Visitor
7 years ago
Solved

Running total with date reset

I am trying to do a fatigue calculation that does a running total of hours worked, and days in a row, but it resets when the person does not come in for work for one day.  I have looked at the past p...
Need Help
jaymccorp's avatar
jaymccorp
Frequent Visitor
7 years ago

That is AMAZING!  It works!!  Now I have to study it for a couple of hours to understand why.

 

THANKS A LOT!!

Shelley's avatar
Shelley
Post Prodigy
6 years ago

Zubair_Muhammad I am trying to do something similar, but want the cumulative total per contract to reset at the end of the contract.

I have contract data like this:

CONTRACT INPUTS    
BPIDCustomer Name (BPID)contract_numSAP Contract StartSAP Contract EndContract Cap
399ABC COMPANY (399)80036564092/1/20181/31/2019 $    8,000.00
399ABC COMPANY (399)80040815792/1/20191/31/2020 $  10,000.00
399ABC COMPANY (399)80045755892/1/20201/31/2021

 $    9,000.00

 

And then I have transaction data. I want to plot the contract cap, which I figured out how to do, AND the cumulative data.

DATA INPUTSDATA INPUTSDESIRED RESULT 
    
Order Submit DateList_PriceCumulative UsageNotes
2/1/2018 0Usage begins at 0 on 2/1/18, which is start of the contract above
2/21/2018$2,592$2,592 
8/23/2018$1,542$4,134 
10/12/2018$1,709$5,843 
10/17/2018$3,893$9,736 
12/4/2018$513$10,249 
2/1/2019 $0Usage begins at 0 again on 2/1/19, with a new contract
2/25/2019$2,940$2,940 
5/14/2019$1,186$4,126 
7/18/2019$0$4,126 
8/23/2019$323$4,449 
9/2/2019$545$4,994 
9/24/2019$2,636$7,630 
2/1/2020 $0Usage begins again at 0 on 2/1/20
2/7/2020$1,737$1,737 
2/19/2020$128$1,865 
2/20/2020$128$1,993 

 

The repair entitlements (blue line) plot correctly, but the cumulative value (red line) keeps accumulating instead of resetting to 0 at the start of a new contract.

 

Here are my formulas:

Repair Entitlement Amount =
CALCULATE(SUM('Entitlements'[Repair Cap]),
FILTER('QBContract','QBContract'[SAP Contract Start] <= MAX('RA_Daily_Calendar'[Date]) &&
'QBContract'[SAP Contract End] >= MAX('RA_Daily_Calendar'[Date])))
 
I know this will not work, but does accumulate:
List_Price running total in Fiscal_YearMonth =
CALCULATE(
    SUM('RepairTransaction'[List_Price]),
    FILTER(
        ALLSELECTED('RA_Daily_Calendar'[CalendarYear-Mo]),
        ISONORAFTER('RA_Daily_Calendar'[CalendarYear-Mo], MAX('RA_Daily_Calendar'[CalendarYear-Mo]), DESC)))
 

For this one I was trying to use the contract dates, but it does not accumulate, it only shows points in time for each repair (orange dots below).

Repair Amount List Price =
CALCULATE(SUM('RepairTransaction'[List_Price]),
FILTER(ALL('RepairTransaction'[Order_Submit_Date]), 'RepairTransaction'[Order_Submit_Date] >= MAX('QBContract'[SAP Contract Start]) &&
'RepairTransaction'[Order_Submit_Date] <= MAX('QBContract'[SAP Contract End])))
 

 

 

Any help is appreciated. Thanks!