Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nimai123
Post Patron
Post Patron

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

Capture1.PNG

 

Reference Image 2

Capture2.PNG

 

Reference Image 3

Capture3.PNG


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

 

Capture4.PNG

@amitchandak 

@Greg_Deckler 

@Ashish_Mathur 

14 REPLIES 14
amitchandak
Super User
Super User

@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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Capture5.PNG

 

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

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 

@Nimai123 ,

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

2.PNG1.PNG

 

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

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Nimai123 ,

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

 

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

I'm missing how you calculated Renewed Open.   

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

 

3.PNG

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

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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

 

Thanks for your patience!

 

Regards,

 

@Greg_Deckler 

@Nimai123 ,

 

You can give try to below measure:

TTM RR = CALCULATE((sum('Table'[Renewed + open w.])/Sum('Table'[ATR])),DATEADD('Table'[Date],-1,QUARTER))
 
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Mariusz
Community Champion
Community Champion

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


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.