Excel to DAX

[Nimai123]

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 3

And 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 

[amitchandak]

@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())

[Greg_Deckler]

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

[Nimai123]

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 

[Greg_Deckler]

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

[Nimai123]

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]

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]

To simplify what I need

For TTM RR

FY19-Q4 = 82%      Requirement

Renewed+open

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

Total by adding =195,330,807

usd_last_year_acv_converted

38,396,325	FY19-Q1
45,537,908	FY19-Q2
79,395,896	FY19-Q3
73,667,240	FY19-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,309	FY19-Q2
66,404,172	FY19-Q3
62,861,772	FY19-Q4
38,341,826	FY20-Q1

Total by adding= 204,220,079

usd_last_year_acv_converted

45,537,908	FY19-Q2
79,395,896	FY19-Q3
73,667,240	FY19-Q4
45,398,559	FY20-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]

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

[Tahreem24]

@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]

I'm missing how you calculated Renewed Open.   

[Tahreem24]

@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]

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

Thanks for your patience!

Regards,

@Greg_Deckler 

[Tahreem24]

@Nimai123 ,

You can give try to below measure:

TTM RR = CALCULATE((sum('Table'[Renewed + open w.])/Sum('Table'[ATR])),DATEADD('Table'[Date],-1,QUARTER))

[Mariusz]

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