Solving for a paycheck amount given a YTD paycheck

[jburklund]

I am trying to write a measure to solve for a paycheck amount given a YTD paycheck amount and paycheck date. Note, this will be run on a very large database so I am trying to avoid using any calculated columns.

Here is a sample table of data: 

The following measure returns the error, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." I know that the filter is returning a table and not a scalar value but I cannot figure out how to get a to calculate as a single value.

Paycheck Amount Calc =

var ytdPayroll = sum(Payroll[ytd_wages_regular_overtime])

var a = CALCULATE(

MAX('Payroll'[ytd_wages_regular_overtime]),

FILTER(ALL(Payroll),

FILTER (Payroll, 'Payroll'[employee_pii_key] = EARLIER('Payroll'[employee_pii_key]) && 'Payroll'[last_check_date] < EARLIER('Payroll'[last_check_date]))))

RETURN

ytdPayroll - a

[amitchandak]

@jburklund , with help from a date table you can use time intelligence

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

[jburklund]

@amitchandak I already have the YTD amount, I do not have the individual paycheck amounts which is what I am trying to solve for. I am trying to find the one previous paycheck ytd amount and subtract it from the most current ytd amount (i.e. 300 - 250 = 50) to find the individual paycheck amounts.

[v-xuding-msft]

Hi @jburklund ,

Please try this calculated column:

Column =
VAR YTD_ = 'Table'[YTD]
VAR a =
    MAXX (
        FILTER (
            'Table',
            EARLIER ( 'Table'[lat_check_date] ) > 'Table'[lat_check_date]
                && EARLIER ( 'Table'[CompanyName] ) = 'Table'[CompanyName]
                && EARLIER ( 'Table'[emplyee_pii_key] ) = 'Table'[emplyee_pii_key]
        ),
        'Table'[YTD]
    )
RETURN
    YTD_ - a

Or

Paycheck Amount Calc =
VAR ytdPayroll =
    Payroll[ytd_wages_regular_overtime]
VAR a =
    CALCULATE (
        MAX ( 'Payroll'[ytd_wages_regular_overtime] ),
        FILTER (
            ALL ( Payroll ),
                'Payroll'[employee_pii_key] = EARLIER ( 'Payroll'[employee_pii_key] )
                    && 'Payroll'[last_check_date] < EARLIER ( 'Payroll'[last_check_date] )
            )
        )
    )
RETURN
    ytdPayroll - a

measure:

Measure = 
VAR YTD_ = CALCULATE(SUM('Table'[YTD]))
VAR a =
    MAXX (
        FILTER (
            ALL('Table'),
            MAX( 'Table'[lat_check_date] ) > 'Table'[lat_check_date]
                && MAX('Table'[CompanyName] ) = 'Table'[CompanyName]
                && MAX('Table'[emplyee_pii_key] ) = 'Table'[emplyee_pii_key]
        ),
        'Table'[YTD]
    )
RETURN
    YTD_ - a

Or

Paycheck Amount Calc =
VAR ytdPayroll =
    SUM(Payroll[ytd_wages_regular_overtime])
VAR a =
    CALCULATE (
        MAX ( 'Payroll'[ytd_wages_regular_overtime] ),
        FILTER (
            ALL ( Payroll ),
                'Payroll'[employee_pii_key] = MAX( 'Payroll'[employee_pii_key] )
                    && 'Payroll'[last_check_date] < MAX( 'Payroll'[last_check_date] )
            )
        )
    )
RETURN
    ytdPayroll - a

[jburklund]

Thank you so much @v-xuding-msft we will try it out!