Running Total with Reset

[dkernen]

I have followed this post https://community.powerbi.com/t5/Desktop/Running-Total-Reset-based-on-Column-Attribute/m-p/251130 but my columns are in different tables.

I have a fact table with the specific needed date, a date table, and a dimension table.  I want to count consecutive successes.

ID	Related_DT	Correct	Consecutive
01072021-083	01/07/2021 19:44	Yes	1
01192021-002	01/19/2021 00:20	Yes	2
01192021-026	01/19/2021 09:41	No	0
01232021-085	01/23/2021 22:41	No	0
01282021-060	01/28/2021 20:40	No	0
02022021-068	02/02/2021 22:24	Yes	1
02182021-051	02/18/2021 16:38	Yes	2
03022021-014	03/02/2021 07:36	Yes	3
03042021-038	03/04/2021 13:42	No	0

I am unclear how to create the MAX date (needed for the running total) since my criteria of "Yes" is in a dimension table and my dates are in my fact table.  I suspect it will be close to something like this, but I am stumped because I need to use multiple tables.  

Measure 4 = MAXX(FILTER(ALLSELECTED(Table4),Table4[Criteria]="Yes"&&Table4[DateMain]<=MAX(Table4[DateMain])),Table4[DateMain])

Run stock = CALCULATE(SUM(Table4[Mvnt Quantity]), DATESBETWEEN(Table4[DateMain],[Measure 4],LASTDATE(Table4[DateMain])))

https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EWZK99sH8PNOt5HzxFFxP3oBGjmSEHKoF1vau...

Any hints/suggestions/measures would be most appreciated!

@running total

[Vera_33 · Accepted solution]

Hi @dkernen 

I've tried below ways, the performance is not good enough, just give you a little hint

test5 is based on your sample measure

test5 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =ADDCOLUMNS(FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT),"Y", RELATED(dimODisp[ORecovered_N]))
VAR MinDT = MAXX(FILTER(T1,[Y]=0),[Referral_DT])
VAR CurY = MAX(dimODisp[ORecovered_N])
RETURN
IF(CurY=1,COUNTROWS(FILTER(T1,[ReferralCall_DT]>=MinDT&&[ReferralCall_DT]<=CurDT)),0)

test4 is another way, to group all 1 together along with the help of test1, test2 and group

test4 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR CurY = MAX(dimODisp[ORecovered_N])
VAR vGroup = [group]
VAR T1= FILTER(ALLSELECTED(factCase),factCase[ReferralCall_DT]<=CurDT&&[group]=vGroup)
RETURN
IF(CurY=1,COUNTROWS(T1),0)

test1 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT)
RETURN
COUNTROWS(T1)

test2 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT)
RETURN
SUMX(T1,RELATED(dimODisp[ORecovered_N]))

group = IF(MAX(dimODisp[ORecovered_N])=1,[test1]-[test2],-1)

[dkernen · Accepted solution]

I wanted to follow-up to show how I made this work.  I wish I could have done with by only creating two measures, but I could not figure out how to get the reset group to work within the Consecutive Count Measure. 

First, I needed a measure from my fact table because I need it later:
Referral_DT Measure = MIN(factCase[Referral_DT]) 

Then I created a grouping for the reset.  (This is the part that I wish I didn't have a separate measure.)

Consecutive Reset_DT =
VAR allselmin = -- required for the initial cases before the first "No"
CALCULATE(
[Referral_DT Measure],
ALLSELECTED(dimODisp[Referral_ID],dimODisp[ORecovered])
)

VAR reset =
MAXX (
CALCULATETABLE(
factCase,
FILTER(
ALLSELECTED ( factCase),
factCase[Referral_DT] <= MAX ( factCase[Referral_DT])
),
dimODisp[ORecovered]="No"
),
factCase[Referral_DT]
)

RETURN COALESCE(reset,allselmin) 

Then the final measure
Consecutive Count=
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR ResetGroup = [Consecutive Reset_DT]
VAR TempTable =
FILTER(
ADDCOLUMNS(
ALLSELECTED(dimODisp), --needed to add the two measures to the table for filtering
"RefDT",[Referral_DT Measure], --surrogate for just taking the referral date
"ResetGrp",[Consecutive Reset_DT]
),
[RefDT] <= CurDT && [ResetGrp] = ResetGroup
)
VAR Consec = CALCULATE(SUM(dimODisp[ORecovered_N]),TempTable)
RETURN Consec

This project took me quite some time to figure out, so I hope I can save time for someone else!

[Vera_33 · Accepted solution]

Hi @dkernen 

I've tried below ways, the performance is not good enough, just give you a little hint

test5 is based on your sample measure

test5 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =ADDCOLUMNS(FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT),"Y", RELATED(dimODisp[ORecovered_N]))
VAR MinDT = MAXX(FILTER(T1,[Y]=0),[Referral_DT])
VAR CurY = MAX(dimODisp[ORecovered_N])
RETURN
IF(CurY=1,COUNTROWS(FILTER(T1,[ReferralCall_DT]>=MinDT&&[ReferralCall_DT]<=CurDT)),0)

test4 is another way, to group all 1 together along with the help of test1, test2 and group

test4 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR CurY = MAX(dimODisp[ORecovered_N])
VAR vGroup = [group]
VAR T1= FILTER(ALLSELECTED(factCase),factCase[ReferralCall_DT]<=CurDT&&[group]=vGroup)
RETURN
IF(CurY=1,COUNTROWS(T1),0)

test1 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT)
RETURN
COUNTROWS(T1)

test2 = 
VAR CurDT = SELECTEDVALUE(factCase[Referral_DT])
VAR T1 =FILTER(ALLSELECTED(factCase),factCase[Referral_DT]<=CurDT)
RETURN
SUMX(T1,RELATED(dimODisp[ORecovered_N]))

group = IF(MAX(dimODisp[ORecovered_N])=1,[test1]-[test2],-1)