Forum Discussion
Running total with date reset
In that case, to get the Days in a Row column we can first add a supporting column which determines when running total is reset
FindCounterReset =
VAR PreviousDate =
MINX (
TOPN (
1,
FILTER (
Table1,
[Employee Number] = EARLIER ( [Employee Number] )
&& [Date] < EARLIER ( [Date] )
),
[Date], DESC
),
[Date]
)
RETURN
IF ( [Date] <> PreviousDate + 1, "Counter reset" )
Now we can add the Days in Row Column as follows
Days in a Row =
VAR counterstart =
MINX (
TOPN (
1,
FILTER (
Table1,
[Employee Number] = EARLIER ( [Employee Number] )
&& [Date] <= EARLIER ( [Date] )
&& [FindCounterReset] = "Counter reset"
),
[Date], DESC
),
[Date]
)
VAR counterend_ =
MINX (
TOPN (
1,
FILTER (
Table1,
[Employee Number] = EARLIER ( [Employee Number] )
&& [Date] > EARLIER ( [Date] )
&& [FindCounterReset] = "Counter reset"
),
[Date], ASC
),
[Date]
)
VAR counterend =
IF ( counterend_ = BLANK (), DATE ( 3000, 1, 1 ), counterend_ )
RETURN
RANKX (
FILTER (
Table1,
[Employee Number] = EARLIER ( [Employee Number] )
&& [Date] >= counterstart
&& [Date] < counterend
),
[Date],
,
ASC,
DENSE
)
Now you can use the Column for running total in the previous post
That is AMAZING! It works!! Now I have to study it for a couple of hours to understand why.
THANKS A LOT!!
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 BPID Customer Name (BPID) contract_num SAP Contract Start SAP Contract End Contract Cap 399 ABC COMPANY (399) 8003656409 2/1/2018 1/31/2019 $ 8,000.00 399 ABC COMPANY (399) 8004081579 2/1/2019 1/31/2020 $ 10,000.00 399 ABC COMPANY (399) 8004575589 2/1/2020 1/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 INPUTS DATA INPUTS DESIRED RESULT Order Submit Date List_Price Cumulative Usage Notes 2/1/2018 0 Usage 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 $0 Usage 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 $0 Usage 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!
