Forum Discussion

skylarw's avatar
skylarw
Frequent Visitor
8 years ago
Solved

Variance in Average Pay

Hey guys,   I'm new to Power BI and I have a hard time following the forums so if this question was already posted somewhere, I apologize.   I have a list of 1300 employee names along with their ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi skylarw,

     

    Try this measure please. You can check it out in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSJ77V2S2Ik1Q7r6

     

    Measure =
VAR lastPaydate =
    CALCULATE (
        MAX ( 'Rows 1 to 1523'[Pay Date] ),
        FILTER (
            ALL ( 'Rows 1 to 1523' ),
            'Rows 1 to 1523'[Pay Date] < MAX ( 'Rows 1 to 1523'[Pay Date] )
        )
    )
VAR lastGrossPay =
    CALCULATE (
        SUM ( 'Rows 1 to 1523'[Gross Pay] ),
        'Rows 1 to 1523'[Pay Date] = lastPaydate
    )
RETURN
    DIVIDE ( SUM ( 'Rows 1 to 1523'[Gross Pay] ) - lastGrossPay, lastGrossPay, 0 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi skylarw,

 

My approach is quite similar to that of v-jiascu-msft.  Since wages are paid fortnightly, here is my calculated field formula for computing the previous fortnight's wages

 

=CALCULATE([Total pay],DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-14,MIN('Calendar'[Date])-14))

Total Pay is

 

=SUM('Rows 1 to 1523'[Gross Pay])