cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkernen
Resolver I
Resolver I

Running Total with Reset

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.

IDRelated_DTCorrectConsecutive
01072021-08301/07/2021 19:44Yes1
01192021-00201/19/2021 00:20Yes2
01192021-02601/19/2021 09:41No0
01232021-08501/23/2021 22:41No0
01282021-06001/28/2021 20:40No0
02022021-06802/02/2021 22:24Yes1
02182021-05102/18/2021 16:38Yes2
03022021-01403/02/2021 07:36Yes3
03042021-03803/04/2021 13:42No0

 

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

2 ACCEPTED SOLUTIONS
Vera_33
Solution Sage
Solution Sage

Hi @dkernen 

 

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

 

Vera_33_0-1623567394832.png

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)

View solution in original post

dkernen
Resolver I
Resolver I

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!

 

View solution in original post

2 REPLIES 2
dkernen
Resolver I
Resolver I

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!

 

View solution in original post

Vera_33
Solution Sage
Solution Sage

Hi @dkernen 

 

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

 

Vera_33_0-1623567394832.png

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)

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors