Forum Discussion

madan27's avatar
madan27
Frequent Visitor
9 years ago
Solved

Month MAX record

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
jvalencia
Frequent Visitor
8 years ago

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