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...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi simid 

Why not? If you have a parameter range then you can use the following measure

bounded_cumulative =
VAR cumulative =
    CALCULATE ( [measure1], ALL ( data ), data[date] <= MAX ( data[date] ) )
VAR MaxBound =
    MAX ( Bound[Value] )
VAR MinBound =
    MIN ( Bound[Value] )
RETURN
    IF (
        cumulative <= MinBound,
        MinBound,
        IF ( cumulative >= MaxBound, MaxBound, cumulative )
    )
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