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

 

DateDepartmentSalary
1/31/2017Engineering80000
1/31/2017HR60000
2/10/2017HR62000
2/13/217Engineering85000
2/28/2017Engineering87000
2/28/2017HR65000
4/11/2017Engineering90000
4/11/2017HR70000

 

From the above table I need to get the below result

 

DateDepartmentSalary
1/31/2017Engineering80000
1/31/2017HR60000
2/28/2017Engineering87000
2/28/2017HR65000
4/11/2017Engineering90000
4/11/2017HR70000

 

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.

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

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    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