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

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-Forum/ba-p/963216 Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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_linkSupporting Data: https://docs.google.com/spreadsheets/d/1ZS01L-gbgo4Yx-6axvqEk7WZjlyMsy-j/edit?usp=drive_link&ouid=111344600491685564806&rtpof=true&sd=trueWorksheet / Scap: https://drive.google.com/file/d/17dcoCnwZWXSEjgVcTkaWVqWKRXPEUqr-/view?usp=drive_link Hope these links work

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 hereRolling 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

please check the link, it asks for access

My apologies thanks for pointing that out.Should be working now KBD

KBD

Helper III

1 year ago

calc a rolling rate

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.

Need Help

11 Replies

lbendlin
Super User
1 year ago
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- KBD
  Helper III
  1 year ago
  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=111344600491685564806&rtpof=true&sd=true
  Worksheet / Scap: https://drive.google.com/file/d/17dcoCnwZWXSEjgVcTkaWVqWKRXPEUqr-/view?usp=drive_link
  
  Hope these links work
- KBD
  Helper III
  1 year ago
  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
    Super User
    1 year ago
    please check the link, it asks for access
KBD
Helper III
1 year ago
Anybody?? This one can't be that difficult. Need some help here 🙏
Ritaf1983 where are you?
KBD
KBD
Helper III
1 year ago
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

Helper III

1 year ago

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

Forum Discussion

calc a rolling rate

11 Replies

Related content

Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

Utilization Rate

Rolling accumulation

Calculate Rolling Sickness Rate From Cumulative DataSet

Rolling accumulation