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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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.

Calculating_ROI_equalizing_point_in_time

 

 

Best Regards,

Dale

thschr's avatar
thschr
Frequent Visitor
8 years ago

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