calculate the percent difference between two paychecks

[sdukes]

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 Paycheck =

    VAR __PREV_ =

        CALCULATE(

            SUM('Payroll Dollars'[Dollars]),

            DATEADD('DateDAX'[Date].[Date], -14, DAY)

        )

    RETURN

        DIVIDE(SUM('Payroll Dollars'[Dollars]) - __PREV, __PREV)

This works if the difference between two paychecks is 14 days. but it isn't always.

My question is how can I use the table with the dates in it to define that 14 day range instead of the fixed value?

for context = my table may look like this:

1/7/2021	$10
1/21/2021	$12
2/8/2021	$9
2/22/2021	$14

(in theory, it's every 14 days - but in reality some of the paychecks are bi-monthly meaning the expected pay date is a weekend, and actual pay is the following day, i.e. a 15 day gap)

I'm looking to reference the dollar value at previous date value (as date appears in the column)

let me know if some further clarification is helpful.

thanks!

[ryan_mayu · Accepted solution]

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

Thank you Ryan! that's super helpful.

Not sure I fully understand it but will keep at it.. 

Is there any way to make this result be filtered by wither filters or slicers? currently it's a fixed value, but I want it to change on a 'per category' basis

[ryan_mayu]

@sdukes 

could you please provide the new sample data and expected output?

[sdukes]

@ryan_mayu, 

this is my current data with implementing your solution above:

but if I filter the right 'Facility' column(using either filter or slicer) to equal 'Be..', the 'days from previous' column keeps the fixed value it has in the row:

instead of, for example:

row 1: blank

row 2: jan22-jan8=16

row 3: feb5-jan22 =14

 my goal is to have the facility filter apply to the code you wrote above, and it recreate the column with the current date difference.. 

[ryan_mayu]

@sdukes 

since this is a visual table, not sample data. i can just have a try.

measure=

VAR lastdate=calculate(max(date),filter(tallexcept(table,table[facilities]),date<max(date)))

return dollars-calculate(sum(dollars),filter(allexcept(table,table[facilities]),date=lastdate))

[sdukes]

Couldn't figure it out... here's some sample data

Year	Quarter	Month	Day	Dollars	Facility
2020	Qtr 1	January	3	$    24,708.03	Ca
2020	Qtr 3	August	14	$    12,135.85	Ca
2020	Qtr 3	September	16	$    26,938.38	Be
2020	Qtr 4	October	14	$    23,981.89	Be
2020	Qtr 4	December	23	$    31,282.14	Be
2020	Qtr 4	December	24	$            64.00	Ca
2020	Qtr 4	December	31	$    13,276.39	Be
2020	Qtr 4	December	31	$    24,093.37	Ca
2021	Qtr 1	February	5	$            96.39	Be
2021	Qtr 2	April	1	$          217.60	Be
2021	Qtr 2	April	15	$            56.66	Be
2020	Qtr 3	July	2	$    29,667.20	Ca
2020	Qtr 3	July	31	$    26,305.27	Ca
2020	Qtr 3	August	19	$    25,385.21	Be
2020	Qtr 4	October	23	$    11,167.20	Ca
2020	Qtr 4	October	28	$    22,942.42	Be
2021	Qtr 1	February	12	$    14,201.05	Ca
2020	Qtr 1	January	17	$    25,138.17	Ca
2020	Qtr 1	January	31	$    24,292.56	Ca
2020	Qtr 1	February	14	$    24,181.89	Ca
2020	Qtr 1	February	28	$    23,609.24	Ca
2020	Qtr 1	March	13	$    24,457.29	Ca
2020	Qtr 1	March	27	$    26,063.86	Ca
2020	Qtr 2	April	10	$    26,385.03	Ca
2020	Qtr 2	April	24	$    28,938.79	Ca
2020	Qtr 2	May	8	$    29,426.91	Ca
2020	Qtr 2	May	22	$    29,613.01	Ca
2020	Qtr 2	June	5	$    28,931.67	Ca
2020	Qtr 2	June	19	$    28,712.81	Ca
2020	Qtr 3	July	17	$    28,813.31	Ca
2020	Qtr 3	August	28	$    11,461.98	Ca
2020	Qtr 3	September	11	$    11,740.76	Ca
2020	Qtr 3	September	25	$    12,402.80	Ca
2020	Qtr 4	October	9	$    11,513.55	Ca
2020	Qtr 4	November	6	$    11,570.28	Ca
2020	Qtr 4	November	20	$    11,619.56	Ca
2020	Qtr 4	December	4	$    17,094.54	Ca
2020	Qtr 4	December	18	$    20,229.14	Ca
2021	Qtr 1	January	15	$    12,959.39	Ca
2021	Qtr 1	January	29	$    13,528.41	Ca
2021	Qtr 1	February	22	$          265.91	Be
2021	Qtr 1	February	26	$    13,383.48	Ca
2021	Qtr 1	March	12	$    12,931.95	Ca
2021	Qtr 1	March	26	$    12,629.75	Ca
2021	Qtr 2	April	9	$    13,202.00	Ca
2021	Qtr 2	April	23	$    12,465.71	Ca
2021	Qtr 2	May	7	$    13,447.80	Ca
2021	Qtr 2	May	21	$    12,533.92	Ca

[ryan_mayu]

@sdukes 

Pls check the attachment below

[sdukes]

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]

@sdukes 

maybe try

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