Require assistance in measures and DAX expressions for my report

[dcvcoe]

I'm new to Power BI and DAX Formulas.  I'm trying to figure out how to work this excel formula in Power BI. 

Excel reportHrs Worked Column - Given valuePTO Earned Column - =[@[Hrs Worked]]*0.0576Accrued Hrs Column - =[@[PTO Earned]]Accrued Hrs Column - =IF(((+E6*0.0576)+G5)>120, (E6*0.0576),((+E6*0.0576)+G5))PTO Used Column - Given valueAccrued PTO Hrs Column - =[@[Accrued Hrs]]Accrued PTO Hrs Column - =(I5+[@[PTO Earned]])-[@[PTO USED]]

[Anonymous]

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

[dcvcoe]

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

[Anonymous · Accepted solution]

Hi @dcvcoe 
Hope I got you correctly.

This is my solution.

• Add an index column to your table.
• Create first custom column (PTOEarned)

_PTO_EARNED = Table523[Hrs Worked] * 0.0576

• Create second custom column (AccruedHrs)
  

_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