Forum Discussion

rymerco's avatar
rymerco
Frequent Visitor
8 years ago

Dynamic Cumulative Subtotal for Correlation

I know...I know...you may think this has already been solved in many ways, but I am not seeing the various examples solve this example I will show you below.

 

I have tried:

Sumx

Filter with Earlier as Column and as standalone measure

Filter with Date <= Max(Date) or within Date Range

 

I literally feel like I have tried all forum topics here and across PowerPivotPros, DaxPatterns, etc.

 

Here's what I am trying to achieve...

 

I'd like to look at a player's cumulative minutes played by each date and same with the team totals.

 

In the example below I want zTeam_CTrlMins to show the cumulative total minutes of zTeam_TtlMins.  Ideally it will be dynamic to the slicer date range selected.  So for zTeam_CTtlMins with these 4 dates it will go:

192 on 1/8/18

144 on 1/7/2018

96 on 1/5/18

48 on 1/2/18

 

Same with the individual zMinsPlayed

119

89

61

34

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I can probably then carry the solution forward on the other stat categories, but none of the solutions I am finding online are creating the answer.  Here is the measure I have in zTeam_CTtlMins:

 

zTeam_CTtlMins = calculate(sumx(values(TeamSummary_Table[Team]),[zTeam_TtlMins]),filter(all(TeamSummary_Table[Date]),TeamSummary_Table[Date]<=max(TeamSummary_Table[Date])))

 

I would also like to create a way to show last 7 games, 5 games and 3 games.  I have tried the TOPN, but it fails horribly.  So if I selected Dec 2017 and Jan 2018 I would want it to smartly pick up that players last (x) based on the selection.

 

3 Replies