max date of each month filter

[harshagraj]

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

[amitchandak]

@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 · Accepted 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]

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.

[Greg_Deckler]

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

[edhans]

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

[Syndicate_Admin]

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?

[harshagraj]

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)

[edhans]

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.

[harshagraj]

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.

[edhans]

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