cancel
Showing results 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

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 help

1 ACCEPTED SOLUTION
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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
10 REPLIES 10
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]))))

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Partisan

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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

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.

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}}),
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

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 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?

Post Partisan

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

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

Super User

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 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
Post Partisan

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.

Super User

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

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

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors