cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Solving for a paycheck amount given a YTD paycheck

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: 

paycheckData.png

 

 

 

 

 

 

 

 

 

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
4 REPLIES 4
Highlighted
Super User IX
Super User IX

Re: Solving for a paycheck amount given a YTD paycheck

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Solving for a paycheck amount given a YTD paycheck

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

Highlighted
Community Support
Community Support

Re: Solving for a paycheck amount given a YTD paycheck

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

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Resolve the amount of a payout check given a YTD payout check

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors