Forum Discussion
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 ...
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
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.
I'm missing how you calculated Renewed Open.
