Forum Discussion

simid's avatar
simid
Regular Visitor
3 years ago

Cumulative Total with Dynamic Parameter Boundaries

Hi all.    I'm new to PowerBI and and having troubling with a cumulative total when trying to add dynamic conditionals.   Here's a sample execl snip of what I'm trying to achieve (or download the...
simid's avatar
simid
Regular Visitor
3 years ago

Thank you tamerj1 

This is really helpful and seems to be very close to solving my problem. I've put the data and measures into a report (download here) and here is a snip;

 

Formulas:

Measure1 = SUM('data'[raw_data1]) - SUM('data'[raw_data2]) + some_param[some_param Value]

cumulative = CALCULATE ( [measure1], ALL ( data ), data[date] <= MAX ( data[date] ) )

bounded_cumulative = 
  VAR cumulative =  CALCULATE ( [measure1], ALL ( data ), data[date] <= MAX ( data[date] ) )
  VAR MaxBound = bound_max[bound_max Value]
  VAR MinBound = bound_min[bound_min Value]
  RETURN
  IF (cumulative <= MinBound,
      MinBound,
      IF ( cumulative >= MaxBound, MaxBound, cumulative )
  )

 

There seems to be something odd going on with the cumulative results.

  • In PowerBI: -1+ 5 = 3, 3 - 8 = -6 ...
  • In Excel: -1 + 5 = 4, 4 - 8 = -4 ...

Am i missing something obvious?

 

Cheers

 

 

 

    • simid's avatar
      simid
      Regular Visitor
      3 years ago

      Thank you again tamerj1 

      I understand cumulative total now, but am still having an issue with the bounded_cumulative. 

      I've managed to get some circular reference errors while trying to account for the previous row.

       

      For example. The second bounded cumulative should be 0 + 5, since the first cumulative was -1 it is replaced with bound_min (0).

       

      Any idea how to get to that? I need to calclate the cumulative (previous cumulative + measure1), check it is within bounds, assign the value or bound, move to the next row and repeat. 

       

      Cheers