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 

      • 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.