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