Calculate salary last year and current year for an employee

[Anonymous]

Hello,

I have to calculate the salary for an employee for last year and the current year.

I have the below data.

The output should be like this.

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

Current Year =
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "Month_Count",
            COUNTROWS (
                FILTER (
                    CALENDAR ( 'Table'[Start date], 'Table'[End date] ),
                    DAY ( [Date] ) = 1
                        && YEAR ( [Date] ) = YEAR ( TODAY () )
                )
            )
    )
VAR t2 =
    ADDCOLUMNS ( t1, "Salary_SUM", [Month_Count] * [Salary] )
RETURN
    SUMX ( t2, [Salary_SUM] )

Last Year =
VAR t1 =
    ADDCOLUMNS (
        'Table',
        "Month_Count",
            COUNTROWS (
                FILTER (
                    CALENDAR ( 'Table'[Start date], 'Table'[End date] ),
                    DAY ( [Date] ) = 1
                        && YEAR ( [Date] ) = YEAR ( TODAY () ) - 1
                )
            )
    )
VAR t2 =
    ADDCOLUMNS ( t1, "Salary_SUM", [Month_Count] * [Salary] )
RETURN
    SUMX ( t2, [Salary_SUM] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I will check. Thank you.

[amitchandak]

@Anonymous , refer if this blog or attached file can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785