Forum Discussion

sdukes's avatar
sdukes
Icon for Helper I rankHelper I
5 years ago
Solved

calculate the percent difference between two paychecks

Hi guys! got a question I don't see an answer for...   I am trying to figure out how to calculate the percent difference between two paychecks.  currently I have:   Percent Paycheck over Payche...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    sdukes 

    is this what you want?

    Column = 
var last=maxx(FILTER('Table','Table'[DATE]<EARLIER('Table'[DATE])),'Table'[DATE])
return maxx(FILTER('Table','Table'[DATE]=last),'Table'[VALUE])

sdukes's avatar
sdukes
Icon for Helper I rankHelper I
5 years ago

Thank you Ryan! this was super helpful. 

the following code is my result that gets me per paycheck rate of change based on the report you shared:

Per Period Percentage Change =
VAR LAST=
MAXX(
FILTER(
ALL('Payroll Dollars'),
'Payroll Dollars'[date] < MAX('Payroll Dollars'[date]) && 'Payroll Dollars'[Facility] = MAX('Payroll Dollars'[Facility])),
'Payroll Dollars'[date]
)

VAR LVALUE=
SUMX(
FILTER(
ALL('Payroll Dollars'),
'Payroll Dollars'[date] = LAST && 'Payroll Dollars'[Facility] = MAX('Payroll Dollars'[Facility])),
'Payroll Dollars'[Dollars]
)

VAR DIFF=
IF(
ISBLANK(LVALUE),
BLANK(),
SUM('Payroll Dollars'[Dollars])-LVALUE
)

VAR Result=
IF(
ISFILTERED(
'Payroll Dollars'[Dollar Categories]),
BLANK(),
DIVIDE(
DIFF,
SUM('Payroll Dollars'[Dollars])
)
)
 
RETURN Result


I'm still struggling to understand how the MAX values work in regards to keeping the filter.. 
what if there was another item that I wanted to add as a filter? i.e. [Dollar Categories] which I'm currently returning a blank if filter is applied.. 

How would I be able to add another filter option to my code?
 
thank you again!
  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    sdukes 

    maybe try

    && 'Payroll Dollars'[Facility] = MAX('Payroll Dollars'[Facility])&&XXXX=MAX(XXXX)),