Forum Discussion

thschr's avatar
thschr
Frequent Visitor
8 years ago
Solved

Calculating ROI equalizing point in time

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 exam...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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)

    Calculating_ROI_equalizing_point_in_time2

     

    Best Regards,

    Dale

Jmenas's avatar
Jmenas
Advocate III
8 years ago

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's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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