calc a rolling rate

[KBD]

Hello: 

Returning to the folks who know a little bit more that I do.

I am struggling to calc a running rate.  I have to be able to calc this rate over dynaminc

(user selected via a slicer ) periods of months.   The months are repesented by an integer ie. 202201, 202202, 202203,  etc..

I grab the slicer high and low and store them in measures yyyyMMHigh and yyyyMMLow.

yyyyMMHigh = MAXX(ALLSELECTED('safetyData'), 'safetyData'[yyyymm]  )
yyyymmLow = MINX(ALLSELECTED('safetyData'), 'safetyData'[yyyymm]  )
works fine.
 
I have gotten a simple single variable monthly aggregation (rolling total) to work as follows:  

this gives me the monthly aggregation of hours worked ( rolling total).  progress.  This becomes my denomitor.   

tstSumHoursWorkedPeriod = Calculate(SUM(safetyData[Hours_Worked]),  FILTER (safetyData, safetyData[yyyymm] >= [yyyymmLow] &&
      safetyData[yyyymm] <= [yyyymmHigh]  &&   safetyData[yyyymm]  <=  MAX(safetyData[yyyymm])))

I do not uderstand what  && safetyData[yyyymm] <= MAX(safetyData[yyyymm])   does in the above. 

For the numerator.

This does NOT work.    Does not aggregate across the months.   Just returns the counts for the individual months.  

sumLostXXXX_RestrictedXXXX = SUMX( FILTER (safetyData, safetyData[yyyymm] >= [yyyymmLow] &&  safetyData[yyyymm] <= [yyyymmHigh]  && 
       safetyData[yyyymm]  <=  MAX(safetyData[yyyymm])), 
	   safetyData[Lost_XXXXX]  +   safetyData[Restricted_XXXXX]  ) 

So, if I get the numerator to work  how do I calc the rate, what does that formula look like, in DAX

   the formula is   (lost_xxxx +   restricted_xxxx ) *  constant   /  hours worked.      

Across the months it would be 

   (sum across the months  lost_xxxx  + sum across the months restricted )  * constant

                  over 

     sum across the months hours worked. 

There , of course,  two other filters in the mix,  they specify location and region. 

So, they have to be left in place. 

Many thanks for your attention to this matter.

KD

[KBD]

Making it even simpler:

Look at the following.

Using the Power BI sheet Rolling II   link is in this trail

This works:

tstDivide = calculate(  sumx( goalAssist,  goalAssist[minutes played] + goalAssist[minutes played]  ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )  

and this works:

tstDivide = calculate(  sumx( goalAssist,  goalAssist[minutes played] * 2  ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )  

but this does not  

tstDivide = calculate(  sumx( goalAssist,  goalAssist[minutes played] / goalAssist[minutes played]  ) , FILTER (AllSelected(goalAssist[yyyymm]), goalAssist[yyyymm] <= MAX(goalAssist[yyyymm]) ) )  

this should return one all day every day, but does not.

Multiplication is also bad.    Strange.  

Just create tstDivide in the Power BI.

Thanks for your attention to this matter.

KBD

[KBD]

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

[KBD]

Anybody??   This one can't be that difficult.   Need some help here  🙏

@Ritaf1983   where are you?

KBD

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[KBD]

Here I am trying to calc a rate that measures soccer player efficiency.   I simple measure to compare players. 

like this  ( goals + ( assists * .5) ) *60 / minutes played.   This mirrors the formula in my real data set. 

Lets go through testGoalAssist  the dashboard.  

Page 1:   contains monthly measures  taking into account the slicers.  All good here

Rolling I:  created measures the accumulate over the months 

               As currently set,  accumulate the goals , assists and minutes played for Mar 2022 - June 2022.

               This works.   Viz in the lower left contains the correct results.  All good here.

Next I want to roll the efficiency measure over the months like this:

  ( sum goals over the months + ( sum assists over the months * .5) ) *60 

      divided by

         sum minutes played over the months

Rolling II:  looking at the viz Top center  

    effectiveRateOverPeriods A : returns the expected results .  

    effectiveRateOverPeriods: is wrong.   Not returning what I expect.

            do not understand what it is doing.  Not the returning my formula above.

Refering to the spreadsheet:  goals_assists_mar_june  at the top you can find 

    a spreadsheet showing what I expect

There are links to to G Drive below.

I cannot explain what is going on here.   It is beyod me.

Many thanks for your assistance.

KD

[lbendlin]

please check the link, it asks for access

[KBD]

My apologies  thanks for pointing that out.

Should be working now

KBD

[lbendlin]

Might be enough to use the average rather than the sum?

G = goals

A = assists

M = minutes

E = average efficiency

[KBD]

No the  data and formula are a mock up for a specific use case involving sensitive data and regulated reporting. 

So, there is no wiggle room in the formula.

KD 

[lbendlin]

I am bowing out at this point. I hope someone else can help you further.

[KBD]

As Ibendlin suggested  have created a mock up which highlights the issue.  It was a good suggestion. 

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