Forum Discussion
calc a rolling rate
Hello:
Trying to keep this simple:
These DAX work:
goalsAccumOverPeriods = Calculate( Sum(goalAssist[goals] ), FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )
assistsAccumOverPeriods = Calculate( Sum(goalAssist[assists] ),FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )
minutesPlayedOverPeriods = Calculate( sum(goalAssist[minutes played] ), FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )
effectiveRate OverPeriods A = calculate( sumx( goalAssist, ( goalAssist[goals] + (goalAssist[assists] * .5)) * 60 ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )
This does not work:
effectiveRate OverPeriods = calculate( sumx( goalAssist, ( goalAssist[goals] + (goalAssist[assists] * .5)) * 60 / goalAssist[minutes played] ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )
As soon as I include the division it goes sideways. Cannot make sence of the results returned.
Links to data and Power BI with viz are below. Sheet Rolling II is where the action is.
Power BI viz: https://drive.google.com/file/d/17dcoCnwZWXSEjgVcTkaWVqWKRXPEUqr-/view?usp=drive_link
Supporting Data: https://docs.google.com/spreadsheets/d/1ZS01L-gbgo4Yx-6axvqEk7WZjlyMsy-j/edit?usp=drive_link&ouid=11...
Worksheet / Scap: https://drive.google.com/file/d/17dcoCnwZWXSEjgVcTkaWVqWKRXPEUqr-/view?usp=drive_link
Hope these links work
Thanks for your attention to this mater.
KD