Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
harshagraj
Post Partisan
Post Partisan

max date of each month filter

Hi all I have a column called Report Date and i need to filter max date of each month and i want to apply this filter in page level.  Please helprprt.JPG

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Well, you could create a column like this:

 

Column =
  VAR __Max = 
    MAXX(
      FILTER(
        'Table',
        MONTH([REPORT_DATE]) = MONTH(EARLIER([REPORT_DATE])) && 
          YEAR([REPORT_DATE]) = YEAR(EARLIER([REPORT_DATE]))
      ),
      [REPORT_DATE]
    )
RETURN
  IF([REPORT_DATE] = __Max,1,0)

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

@harshagraj , better you have month year column in table or date table

calculate([measure],values(Table[Month-Year]),filter(Table,Table[Report] =LASTNONBLANKVALUE(Table[Month-Year], max(Table[Report]))))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Greg_Deckler
Super User
Super User

Well, you could create a column like this:

 

Column =
  VAR __Max = 
    MAXX(
      FILTER(
        'Table',
        MONTH([REPORT_DATE]) = MONTH(EARLIER([REPORT_DATE])) && 
          YEAR([REPORT_DATE]) = YEAR(EARLIER([REPORT_DATE]))
      ),
      [REPORT_DATE]
    )
RETURN
  IF([REPORT_DATE] = __Max,1,0)

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you so much @Greg_Deckler  it worked!! but just in case any possibility to do this in query level? so that i can save the refresh time.

@harshagraj Hmm, perhaps @ImkeF or @edhans has a thought here.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Is this what you need? A column in Power Query that has the maximum date from your data for each given month?
EDIT:  I added an [IsMaxDate] true/false column in the M code to add true if ithe date is the max date, or false if not. I didn't bother redoing the image though. 

2020-05-25 12_05_49-Untitled - Power Query Editor.png

 

My solution adds a temporary column called YearMonth (YYYYMM format) to do a row selection on. Here is the M code.

The key is the #"Added Max Date" step.

  1. It holds the current record YearMonth in a variable
  2. It creates a table in memory of all dates in that month (yearmonth)
  3. It finds the maximium date in that table
  4. converts that date to a value in the new Max Date column

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBDYAwDAN36RsptYHSzlKx/xqEfrAf/HI5WbEyZ2Hswcpa7m0WMJCEsYg5muOlsosLNqPuuaq5tRDZ7KKXOT+HgDbNI4fItDDk0KjVY/w7mMN6zkv3Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added YearMonth" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*100 + Date.Month([Date]), Int64.Type),
    #"Added Max Date" =
        Table.AddColumn(#"Added YearMonth", "Max Date", 
        each
            let
                varDate = [YearMonth]
            in
            Table.Max(
                Table.SelectRows(#"Added YearMonth", each [YearMonth] = varDate)
                , "Date"
            )[Date]
, type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Max Date",{"Date", "Max Date"}),
    #"Added IsMaxDate" = Table.AddColumn(#"Removed Other Columns", "IsMaxDate", each [Date] = [Max Date], type logical)
in
    #"Added IsMaxDate"

 

 

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Good morning. It's interesting what you're up to here. I'm looking for something similar but not the maximum date per month, but the max date of that whole column.

How could I achieve that?

Hi @edhans thanks for the solution but i am getting in the below step.

#"Added YearMonth" = Table.AddColumn(#"Changed Type3", "YearMonth", each Date.Year([REPORT_DATE])*100 + Date.Month([REPORT_DATE])),

#"Added Max Date" =
Table.AddColumn(#"Added YearMonth", "Max Date",
each
let
varDate = [YearMonth]
in
Table.Max(
Table.SelectRows(#"Added YearMonth", each [YearMonth] = varDate)
, "Date"
)[REPORT_DATE]
, type date),
#"Added IsMaxDate" = Table.AddColumn(#"Added Max Date", "IsMaxDate", each [REPORT_DATE] = [Max Date], type logical)
tempsnip.jpg

What does the error say? Click on the gray box next to the word Error and PQ will show you the error contents at the bottom of your file.

You might get rid of the ",type date" portion. If you aren't returning a legit date, that will cause an error to try and set the date type as a date.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi @edhans thank you. It worked for a small data. Now i applied this for a data where it has 7 L rows and it is very slow because it is applying for each row.

@harshagraj - check out this article. It may help. My solution is definitly not for large data sets.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.