Dynamic Cumulative Subtotal for Correlation

[rymerco]

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.

[Greg_Deckler]

Well, without actual sample data, pretty hard to give you an exact answer. 

But, what you are describing seems like a Running Total quick measure or some slight variant would work just fine. I would probably use a SUMMARIZE to summarize the running totals into some new tables and then use your standard pearson correlation, the technique for which was originally published here ages ago (2 years ago):

https://community.powerbi.com/t5/Community-Blog/Correlation-Seasonality-and-Forecasting-with-Power-B...

and before that published in 2015 here:

https://social.technet.microsoft.com/wiki/contents/articles/33184.power-bi-correlation-seasonality-a...

And just recently recycled as a Quick Measure in the January release of Desktop and accredited to someone else. But I'm not bitter or anything. 🙂

[Ashish_Mathur]

Hi,

Your question is not clear.  Please share a dataset and show the expected result there.

[rymerco]

Sorry about the confusion Ashish and community, but your help is appreciated.

Link to the PBi file: https://www.dropbox.com/s/qrv8laj1qxhqcy2/PlayerMins_PowerBI.v1.pbix?dl=0

Here is a snapshot of the goal:

Here is a snapshot of the PowerBi file: