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 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!

 

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

6 Replies

  • 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