Forum Discussion

SebboBI's avatar
SebboBI
Frequent Visitor
3 years ago

Get First Occurence on Running Sum Measure

Hi P BI Com, 

 

I screened many threads for hours and even though some solution sound very close, so far I couldnt find a working one. I need your help! 🙂

 

My Case:

I have a table with a time column (in years) and many more columns that stand for different costs and revenues (not shown for simplicity).

I calculate a measure "EBITDA" which sums all revenues and costs.

Furthermore, I calculate the Running Sum of EBITDA, which shows the cumulative profitability over time.

 

My Problem:

Now I want to calculate the Break Even Point -> When is the exact time that this running sum is becoming profitable (Sum EBITDA > 0). It should actually be just a single value that I would like to display on my dashboard!

 

So far, I used a calculate expression that nests an if coniditon to calculate the exact point in time only if Running Sum EBITDA is > 0.

The calculation itself works correctly. However, it is in some weird "format" that I can not process correctly in any of the wanted visuals or furher calculations. The column sum "pretends" to be inifinity ?!?!

How can I get an expression that just returns the first real occurence of column "Break Even Point"?

 

I feel that part of my problem is that the measure uses the Running Sum which gives wrong values when evaluated in a sumx (line by line). The expression of the Running Sum is 

Running Sum EBITDA =
CALCULATE(
    [EBITDA],
    FILTER(
        ALLSELECTED('Time Series'[Time]),
        ISONORAFTER('Time Series'[Time], MAX('Time Series'[Time]), DESC)
    )
)

 

Data from the Screenshot:

TimeEBITDARunning Sum EBITDABreak Even Point
1-1882920000-1882920000 
2118486966-1764433034 
3253469373-1510963661 
4253469373-1257494288 
5253469373-1004024915 
6253469373-750555542 
7253469373-497086169 
8253469373-243616796 
925346937398525779,96
102534693732633219509,96

 

1 Reply