Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm new to Power BI and DAX Formulas. I'm trying to figure out how to work this excel formula in Power BI.
Solved! Go to Solution.
Hi @dcvcoe
Hope I got you correctly.
This is my solution.
_PTO_EARNED = Table523[Hrs Worked] * 0.0576
_ACCURED_HRS = VAR _CURRENT_INDEX = Table523[Index] // or any predicate-like column VAR _PREV_INDEX = CALCULATE ( COUNTROWS ( Table523 ), FILTER ( Table523, Table523[Index] < _CURRENT_INDEX ) ) VAR _PREV_ACCURED = CALCULATE(SUM(Table523[_PTO_EARNED]), FILTER(Table523, Table523[Index] = _PREV_INDEX)) VAR _TEMP = (Table523[_PTO_EARNED] + _PREV_ACCURED) VAR _ANSWER = IF( _TEMP > 120, Table523[_PTO_EARNED], _TEMP) RETURN _ANSWER
Not sure about the third calculation you need, but maybe you can continue from here?
Cheers!
A
Hey @dcvcoe
It is better to give us some sample data and the expected results.
It is hard to follow your screenshots.
+ Better be copy and pasteble data.
Cheers!
A
Hi,
Here is the link of the sample Excel report Data.
https://drive.google.com/open?id=1NhvPcf6cQevSWwQmGyQKH5kDZAHeNftU
The expected results would be like this table
HrsActual, Accrual_Rate and PTO_Used is already given based from datasource.
Excel Formula:
PTOEarned =[@[Hrs Worked]]*0.0576
Note: (HrsActual * Accrual_Rate)
AccruedHrs:
First Row =[@[PTO Earned]]
Second Row = IF(((+E6*0.0576)+G5)>120, (E6*0.0576),((+E6*0.0576)+G5))
Note:
Condition: (HrsActual * Accrual_Rate) + first row value of AccruedHrs is greater than 120 then
(HrsActual * Accrual_Rate) else ((HrsActual * Accrual_Rate) + first row value of AccruedHrs )
AccruedPTOHrs:
First Row =[@[Accrued Hrs]]
Second Row = I=(I5+[@[PTO Earned]])-[@[PTO USED]]
Hi @dcvcoe
Hope I got you correctly.
This is my solution.
_PTO_EARNED = Table523[Hrs Worked] * 0.0576
_ACCURED_HRS = VAR _CURRENT_INDEX = Table523[Index] // or any predicate-like column VAR _PREV_INDEX = CALCULATE ( COUNTROWS ( Table523 ), FILTER ( Table523, Table523[Index] < _CURRENT_INDEX ) ) VAR _PREV_ACCURED = CALCULATE(SUM(Table523[_PTO_EARNED]), FILTER(Table523, Table523[Index] = _PREV_INDEX)) VAR _TEMP = (Table523[_PTO_EARNED] + _PREV_ACCURED) VAR _ANSWER = IF( _TEMP > 120, Table523[_PTO_EARNED], _TEMP) RETURN _ANSWER
Not sure about the third calculation you need, but maybe you can continue from here?
Cheers!
A
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |