cancel
Showing results for
Did you mean:
dcvcoe Frequent Visitor

## Require assistance in measures and DAX expressions for my report

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 report Hrs Worked Column - Given value PTO Earned Column - =[@[Hrs Worked]]*0.0576 Accrued Hrs Column - =[@[PTO Earned]] Accrued Hrs Column - =IF(((+E6*0.0576)+G5)>120, (E6*0.0576),((+E6*0.0576)+G5)) PTO Used Column - Given value Accrued PTO Hrs Column - =[@[Accrued Hrs]] Accrued PTO Hrs Column - =(I5+[@[PTO Earned]])-[@[PTO USED]]

1 ACCEPTED SOLUTION

Accepted Solutions
AClerk New Contributor

## Re: Require assistance in measures and DAX expressions for my report

Hi @dcvcoe
Hope I got you correctly.

This is my solution.

• 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

Not sure about the third calculation you need, but maybe you can continue from here?

Cheers!
A

3 REPLIES 3
AClerk New Contributor

## Re: Require assistance in measures and DAX expressions for my report

Hey @dcvcoe

It is better to give us some sample data and the expected results.

+ Better be copy and pasteble data.

Cheers!
A

dcvcoe Frequent Visitor

## Re: Require assistance in measures and DAX expressions for my report

Hi,

Here is the link of the sample Excel report Data.

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]] AClerk New Contributor

## Re: Require assistance in measures and DAX expressions for my report

Hi @dcvcoe
Hope I got you correctly.

This is my solution.

• 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

Not sure about the third calculation you need, but maybe you can continue from here?

Cheers!
A

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  Top Kudoed Authors
Users Online
Currently online: 179 members 1,778 guests
Recent signins:
• mstotsky • pratapchava • abishek108 • cmeu • ajerovich • crenninger • mgreez • DLeopold • srinivasu • ldenney • evgeniam • KBGSP • jameslee • TristanHindes 