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 Reference Image 2 Reference Image 3And so on=SUM(O14:O17)/SUM(D14:D17)=SUM(O15:O18)/SUM(D15:D18)=SUM(O16:O19)/SUM(D16:D19) Measure I created TTM RR = CALCULATE([Divide],DATESYTD(DATEADD(derived_dimdate[fulldate],-4,QUARTER))) Output amitchandak Greg_Deckler Ashish_Mathur

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.

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

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.

Nimai123 , Take rolling of Qtr Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,QUARTER)) HAs full QTR= CALCULATE(distinctcount(Date[Date),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,QUARTER)) Final Rolling 4 = if([HAs full QTR]=4,[Rolling 4 ],blank())

Excel to DAX | Microsoft Fabric Community

14 Replies

amitchandak
Super User
6 years ago
Nimai123 , Take rolling of Qtr

Rolling 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,QUARTER))

HAs full QTR= CALCULATE(distinctcount(Date[Date),DATESINPERIOD(Table[Date],ENDOFMONTH(Sales[Sales Date]),-4,QUARTER))

Final Rolling 4 = if([HAs full QTR]=4,[Rolling 4 ],blank())
Mariusz
Community Champion
6 years ago
Hi Nimai123

You can use DATESINPERIOD
https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

but you will need to adjust your model, add a date field to your table and Date dimension
https://www.youtube.com/watch?v=Pzn2B4laK9A

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn
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
  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
    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))