Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
104 | |
101 | |
80 | |
67 |