Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have Payroll data for all years in a table. I am trying to create a custom table which should bring me the last payroll i.e, max(salary_year) and max(salary_month)
I tried creating it as below, but I can use only one filter, I need to add 'PAYROLL'[Salary_YEAR] = MAX('PAYROLL'[Salary_YEAR]). Please support.
--------------------------------------------
Solved! Go to Solution.
Try this calculated table:
Last Sal = VAR _MaxYearMonth = MAXX ( ALL ( 'PAYROLL'[Salary_month], 'PAYROLL'[Salary_YEAR] ), 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_Month] ) RETURN FILTER ( 'PAYROLL', 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_month] = _MaxYearMonth )
You could also create a calculated column in the original table with YearMonth and then filter based on that.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.
Salary Filter = IF ( 'PAYROLL'[Salary_month] = MAX ( 'PAYROLL'[Salary_month] ) && 'PAYROLL'[Salary_YEAR] = MAX ( 'PAYROLL'[Salary_YEAR] ), "Las Sal", Blank() )
You can add this filter to your table visual filters pane and check only Last Sal.
Hi @mussaenda
Could you elaborate on this please...
I need a filtered table giving me only the fields from max salary month and max salary year filters.
This is what I meant with the formula. If I am not mistaken, this is what you need, right? @shoebhakeem123
Please see snapshots of the sample table and the expected resultant table.
Here in the table you will find years 2018 and 2019 in the sal_year Column, and month 12, 1, 2 in the Sal_month column. I need a filtered table which gives me the last salary year and salary month. Here in this case, year 2019 and month = 02 as shown in the expected table below.
Please support.
Try this calculated table:
Last Sal = VAR _MaxYearMonth = MAXX ( ALL ( 'PAYROLL'[Salary_month], 'PAYROLL'[Salary_YEAR] ), 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_Month] ) RETURN FILTER ( 'PAYROLL', 'PAYROLL'[Salary_YEAR] * 100 + 'PAYROLL'[Salary_month] = _MaxYearMonth )
You could also create a calculated column in the original table with YearMonth and then filter based on that.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.
How about this:
ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Table'[Employee_code], 'Table'[Employee_name], "Sal_Year", MAX ( 'Table'[Sal_Year] ) ), "Sal_Month", VAR LastYear = [Sal_Year] RETURN CALCULATE ( MAX ( 'Table'[Sal_Month] ), 'Table'[Sal_Year] = LastYear ) ), "Net Salary", VAR LastYear = [Sal_Year] VAR LastMonth = [Sal_Month] RETURN CALCULATE ( MAX ( 'Table'[Net Salary] ), 'Table'[Sal_Year] = LastYear, 'Table'[Sal_Month] = LastMonth ) )
How about this:
Last Sal = FILTER ( 'PAYROLL', 'PAYROLL'[Salary_month] = MAX ( 'PAYROLL'[Salary_month] ) && 'PAYROLL'[Salary_YEAR] = MAX ( 'PAYROLL'[Salary_YEAR] ) )
or if you want it a bit more efficient:
Last Sal = VAR _MaxMonth = MAX ( 'PAYROLL'[Salary_month] ) VAR _MaxYear = MAX ( 'PAYROLL'[Salary_YEAR] ) RETURN FILTER ( 'PAYROLL', 'PAYROLL'[Salary_month] = _MaxMonth && 'PAYROLL'[Salary_YEAR] = _MaxYear )
Hey, thank you for your response.
But I did try this method before but somehow the &&s do not work with filter.
Returns a blank table. Maybe I am doing something wrong.
User | Count |
---|---|
99 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |