Month MAX record

[madan27]

Hi 

I need to filter the records based on Mnth MAX date avillable within the table

I am having a table as below

Date	Department	Salary
1/31/2017	Engineering	80000
1/31/2017	HR	60000
2/10/2017	HR	62000
2/13/217	Engineering	85000
2/28/2017	Engineering	87000
2/28/2017	HR	65000
4/11/2017	Engineering	90000
4/11/2017	HR	70000

From the above table I need to get the below result

Date	Department	Salary
1/31/2017	Engineering	80000
1/31/2017	HR	60000
2/28/2017	Engineering	87000
2/28/2017	HR	65000
4/11/2017	Engineering	90000
4/11/2017	HR	70000

As a Part of January '1/31/2017' is MAX Date and get all the rows that fall under this date.

In the month of April '4/11/2017' is the MAX data that is avillable within the table.

I am using direct query, kindly suggest some solutions to achive this without using import.

Thanks in advance.

[v-huizhn-msft]

Hi @madan27,

If you have resolved your issue, please mark the right  reply or share your solution as workaround. So that more people will benefit from it.

Thanks,
Angelia

[jvalencia]

I tried to use the solution, but it gave me the message:

Function 'SELECTCOLUMNS' is not supported in DirectQuery mode.

When I was trying to set the formula table

[v-huizhn-msft · Accepted solution]

Hi @madan27,

You can get max date in one month, then filter it based on the new column, please follow up the following steps.

First, create calculated columns using the formulas.

Month = MONTH(Test[Date])
Max_date_in_one_month = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Month]))

Then, click "New table" under modeling on home page, create a new table using the formula.

Table = SELECTCOLUMNS(FILTER(Test,Test[Date]=Test[Max_date_in_one_month]),"Date",Test[Date],"Department",Test[Department],"Salary",Test[Salary])

Please feel free to ask if you have any other issue.

Best Regards,
Angelia