Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
6 years ago

Excel to DAX

I have a formula in excel which is calculating the percentage only if it has data for above 4 qtr   How can I convert this in a DAX expression using this logic of the formula   Reference image 1 ...
Need Help
Nimai123's avatar
Nimai123
Post Patron
6 years ago

 

This relationship is based on the date, taking the date column for the derived_dimdate and rest coming from the renewal_reporting

 

Greg_Deckler 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

You're killing me Nimai123 

 

So for the sample data that you posted, the first two are columns in the left table in your image and the last one is a column in your date table or ? 

 

Please follow the instructions here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Sample data as text in the same form that it appears in your data. Expected result from sample data. Logic of how to get from a to b.

  • Nimai123's avatar
    Nimai123
    Post Patron
    6 years ago

    I have sent you the link for the file to download in Private Message. 

     

    Thanks for your patience!

     

    Regards,

     

    Greg_Deckler 

  • Nimai123's avatar
    Nimai123
    Post Patron
    6 years ago

    To simplify what I need

     

    For TTM RR

     

    FY19-Q4 = 82%      Requirement

     

    Renewed+open

    29,452,554FY19-Q1
    36,612,309FY19-Q2
    66,404,172FY19-Q3
    62,861,772FY19-Q4

    Total by adding =195,330,807

     

    usd_last_year_acv_converted

    38,396,325FY19-Q1
    45,537,908FY19-Q2
    79,395,896FY19-Q3
    73,667,240FY19-Q4

    Total by adding = 236,997,369

     

    195,330,807/236,997,369 = TTM RR = FY19-Q4 = 82%

     

    FY20-Q1 = 84%      Requirement

     

    Renewed+open

    36,612,309FY19-Q2
    66,404,172FY19-Q3
    62,861,772FY19-Q4
    38,341,826FY20-Q1

    Total by adding= 204,220,079

     

    usd_last_year_acv_converted

    45,537,908FY19-Q2
    79,395,896FY19-Q3
    73,667,240FY19-Q4
    45,398,559FY20-Q1

    Total by adding = 243,999,603

     

    204,220,079/243,999,603= TTM RR =FY20-Q1 = 84%

     

    And same for the other periods, but it should only calculate when we have data for the previous 4 Qtrs including itself.

     

    To understand the above statement we are not calculating FY19-Q3 because we have data for  FY19-Q3, Q2, Q1 but no data for FY18-Q4

     

    Hope this helps

     

     

    Greg_Deckler 

    Tahreem24 

    • Tahreem24's avatar
      Tahreem24
      Super User
      6 years ago

      Nimai123 ,

      Let me know is this what you wanted:
      SS for your reference:
       
      Dont forget to give THUMBS UP and accept this as a SOLUTION if it helped you.

       

      • charleshale's avatar
        charleshale
        Continued Contributor
        3 years ago

        I'm missing how you calculated Renewed Open.   

    • Tahreem24's avatar
      Tahreem24
      Super User
      6 years ago

      Nimai123 ,

       

      Step 1: Perform cumultaive sum of ATR and Renewed Open fields.

      Step 2: Divide this cumulative sum and represent them in percentage format.

       

      Below is the result of FY19-Q4.

       

      Give Thumbs up to this efforts and accept this as a solution if it helped you.

    • Tahreem24's avatar
      Tahreem24
      Super User
      6 years ago

      Nimai123 ,

      Please verify that my Screen shot and your Excel is matching perfectly.

       

      Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.