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

5 Replies

  • 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