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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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]))))

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors