cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcvcoe Frequent Visitor
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. 

 

1.pngExcel report2.pngHrs Worked Column - Given value3.pngPTO Earned Column - =[@[Hrs Worked]]*0.05764.pngAccrued Hrs Column - =[@[PTO Earned]]5.pngAccrued Hrs Column - =IF(((+E6*0.0576)+G5)>120, (E6*0.0576),((+E6*0.0576)+G5))6.pngPTO Used Column - Given value7.pngAccrued PTO Hrs Column - =[@[Accrued Hrs]]8.pngAccrued PTO Hrs Column - =(I5+[@[PTO Earned]])-[@[PTO USED]]



1 ACCEPTED SOLUTION

Accepted Solutions
AClerk New Contributor
New Contributor

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

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

 

 

 

 

 

View solution in original post

3 REPLIES 3
AClerk New Contributor
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.

It is hard to follow your screenshots.

+ Better be copy and pasteble data.

Cheers!
A

dcvcoe Frequent Visitor
Frequent Visitor

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

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


9.png

 

 

AClerk New Contributor
New Contributor

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

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

 

 

 

 

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

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.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

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.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 179 members 1,778 guests
Please welcome our newest community members: