cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Output

14 REPLIES 14
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())

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

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

Super User

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 ?

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

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

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

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

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

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

Super User

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.

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
Super User
Let me know is this what you wanted:

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
Responsive Resident

I'm missing how you calculated Renewed Open.

Super User

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.

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
Post Patron

Regards,

Super User

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
Community Champion

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

Best Regards,
Mariusz

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

Please feel free to connect with me.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors