Cumulative Total with Dynamic Parameter Boundaries

[simid]

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 xlsx here)

I have a date, raw_data and 3 dynamic parameters. I calculated measure1 using the raw_data and a dynamic user parameter some_param. A cumulative measure is easy enough in PowerBI:

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

But I can't work out how to replicate a bounded cumulative i.e. if the cumulative is <= bound_min it stays bound_min or if it is >= bound_max it stays bound_max. I thought a measure like this would work, but no. 

bounded_cumulative = 
var cumulative = CALCULATE(
    [measure1], 
    ALL (data),
    data[date] <=  MAX(data[date])
)
return
if (cumulative <= bound_min[bound_min Value], bound_min[bound_min Value], 
  if(cumulative >= bound_max[bound_max Value], bound_max[bound_max Value], cumulative)
)

The examples I've read with conditionals and cumulatives use calculated columns but (from my understanding) I won't be able to use dynamic paramaters in that case?

Any suggestions?

Thank you.

[tamerj1]

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]

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

[tamerj1]

@simid 

Please refer to attached file

[tamerj1]

@simid 

This one after fixing the Bounded Measure

[simid]

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