Calculating ROI equalizing point in time

[thschr]

Hi everyone,

I am aiming to calculate an "equalizing" point measure, which indicates when the costs will be equalized by the net revenue. 

The measure would constitute a column that  for example indicates "Juni 2018" in the row "Mai 2018". I am aware that the rest would be empty for the example below. (The goal is to add the equalizing point for "Juni 2018" when we go further in time and net revenue increases.) Time-wise we're looking a monthly cohorts here.

What I've got in mind is a running total calculation, for which I unfortunately don't know how to calculate the measure for cost vs. net revenue over time for. Can anybody help out or give me a starting point? Any help appreciated! Thank you very much!

[v-jiascu-msft]

Hi @thschr,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @thschr,

I would suggest you add a new calculated column and a date table to leverage the power of time intelligence functions. Please check out the demo in the attachment.

Measure =
CALCULATE (
    SUMX ( 'Table1', Table1[Net Revenue] - Table1[Cost] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( Table1[startOfMonth] )
    )
)

If the measure is equal to or great than 0, that means the revenue covers the cost.

Best Regards,

Dale

[thschr]

Hi @v-jiascu-msft, 

Thank you very much - however what I intend is more of a cross-monthly measure instead of a running total. 

Meaning: I'd like to see when the monthly costs can be covered by the net revenue in time. Another example: 

Here, the costs of May 2018 would be covered in June because Cost 4.200 € - (Net Revenue 2400 € (May) + Net Revenue 2000 € (June)) = - 200 €

For the following month June: 2900 € Cost - (3900 € Net Revenue (July)+ 200 € net revenue leftover from previous months) = - 1200 €, so the equalizing point is in July.

I've tried my luck with running totals for this one, but I couldn't come up with a solution so far. 

[v-jiascu-msft · Accepted solution]

Hi @thschr,

As far as I know, I'm afraid we can't achieve that by DAX. I would suggest you try the R. Please check out the demo in the attachment.

dataset <- dataset[with(dataset, order(Index)),]
j <- 1
middle_value <- 0
new_col <- c()
nrows_dataset <- nrow(dataset)
for (i in c(1: nrows_dataset)) {
    if (middle_value == 0) {
        middle_value <- dataset$Cost[i] - dataset$"Net Revenue"[j]
        j <- j + 1
    }
    else {
        middle_value <- dataset$Cost[i] + middle_value
    }
    while(middle_value > 0 && j <= nrows_dataset) {
        middle_value <- middle_value - dataset$"Net Revenue"[j]
        j <- j + 1
    }
    if (middle_value <= 0) {
        new_col[i] <- as.character(dataset$Month[j - 1])
    }
    else {
        new_col[i] <- "null"
    }
}
result <- cbind(dataset, new_col)

Best Regards,

Dale

[Jmenas]

hi @v-jiascu-msft 

I was also trying to check on this. BTW do you know a way to export as a table? 
We tried with R library tableHtml and didn't work. Could you recommend me how to go on? or if I need another package?

best,
J

[v-jiascu-msft]

Hi @Jmenas,

What could be the result of "export as a table"? Usually, we won't export data from the Query Editor. If you'd like to know how to return the result of R to Power BI, you just need to format the result in the type of data.frame of R explicitly like below. 

result <- cbind(dataset, new_col)

If this isn't the case, I would suggest you open a new thread in this forum that we can discuss more.

Best Regards,

Dale