Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
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)
@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]))))
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)
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.
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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGood 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)
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |