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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Can you post sample data as text so that we don't have to type it all in to recreate it?

 

Seems like you have a separate date table, so that is good for time intelligence functions. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for. Using this technique you have explicit control over what dates you are calculating versus relying on time intelligence functions which don't always act the way you think they would.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

  • Nimai123's avatar
    Nimai123
    Post Patron
    6 years ago

    Sample Data 

    ATR
    38,396,325
    45,537,908
    79,395,896
    73,667,240
    45,398,559
    51,738,530
    98,312,211
    77,909,807
    49,622,724
    51,807,342
    86,244,385
    85,776,095

     

    Renewed + open w.
    29,452,554
    36,612,309
    66,404,172
    62,861,772
    38,341,826
    42,568,263
    81,921,358
    63,845,235
    41,543,047
    44,173,611
    74,205,335
    73,457,709

     

    FQ
    FY19-Q1
    FY19-Q2
    FY19-Q3
    FY19-Q4
    FY20-Q1
    FY20-Q2
    FY20-Q3
    FY20-Q4
    FY21-Q1
    FY21-Q2
    FY21-Q3
    FY21-Q4

    Greg_Deckler 

    • Tahreem24's avatar
      Tahreem24
      Super User
      6 years ago

      Nimai123 ,

       

      You can give try to below measure:

      TTM RR = CALCULATE((sum('Table'[Renewed + open w.])/Sum('Table'[ATR])),DATEADD('Table'[Date],-1,QUARTER))
       
       
    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Are those 3 separate tables? How are they related to one another?

      • 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