Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Sum measured values from previous rows

Hi, I have a question on how to summarize my revenue based on measured values from previous fiscal period. For context:

 

(Dummy datatable for demonstration purpose)

 

My company tracks our order using fiscal period. Using Power BI, we can sum the total order we received in each period. 

 

Within the total order, we estimate that 20% of it will be paid within the same fiscal period, while 80% will be paid within the quarter period - to simplify, we assume that the 80% amount will be paid 3 months later. 

 

Based on the screenshot I attached, you can see that I'm trying to calculate the estimated revenue by adding the monthly invoice and quarterly invoice from 3 months ago. So estimated revenue of F20P04 = 20% of F20P04 order + 80% of F20P01 order. 

 

Is there a way to calculate the Estimated Revenue in Power BI?

Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    3 years ago

    Hello Anonymous , before filter in variable B use ALLEXCEPT(TABLE,'TABLE'[Period]). I believe it will help you. 

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

3 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    3 years ago

    Hello Anonymous ,

    Here is the solution as per your example. If you are applying to larger data then you may need to modify as per your requirement. 

    First creating Ranking column by considering right of the Period Column and its calculation looks like this:

    Ranking = RIGHT('Table'[Peroid],2)

    Then convert it into whole number. 

    Then create Estimated Revenue Column using below calculation:

    Estimated Revenue = 
Var Ref = 'Table'[Ranking]-3
Var A = CALCULATE(SUM('Table'[Measured Monthly Invoice (20%)]))
Var B = CALCULATE(SUM('Table'[Measured Quarterly Invoice (80 %)]),FILTER('Table', 'Table'[Ranking] = Ref))
Return
IF('Table'[Ranking] = 1 || 'Table'[Ranking] = 2 || 'Table'[Ranking] = 3 , BLANK(),A+B)

    Your output looks as below:

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Kishore_KVN

       

      Really appreciate the help 🙂

       

      Perhaps I should have mentioned earlier on that all 3 of the columns (Sum of Order, Measured Monthly Invoice (20%), and Measured Quarterly Invoice (80%)) in my example are measure columns.

       

      When I tried testing the formulas you shared, this happened:

      Based on the screenshot, the formula actually added the quarterly invoiced to all the order rows. For instance, if you look at FY20P04, there is 2 orders with the quantity 40 and 60. When I added in the Estimated Revenue formula, it added 72 to both of the rows. 

       

      For your reference, these are my formulas:

       

      Measured Monthly Invoice (20%) = SUM('Table'[Order])*0.2
       
      Measured Quarterly Invoice (80%) = SUM('Table'[Order])*0.8
       
      Estimated Revenue =
      Var Ref = 'Table'[Ranking]-3
      Var A = CALCULATE([Measured Monthly Invoice (20%)])
      Var B = CALCULATE([Measured Quarterly Invoice (80%)],FILTER('Table', 'Table'[Ranking] = Ref))
      Return
      IF('Table'[Ranking] = 1 || 'Table'[Ranking] = 2 || 'Table'[Ranking] = 3 , BLANK(),A+B)
       
      Again, thank you for helping me with my problem. Really appreciate it 🙂
      • Kishore_KVN's avatar
        Kishore_KVN
        Solution Sage
        3 years ago

        Hello Anonymous , before filter in variable B use ALLEXCEPT(TABLE,'TABLE'[Period]). I believe it will help you. 

        If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!