DAX Power BI select only max date rows of each month and year

[Anonymous]

Hello, 

i've a table (left one), i want to apply a dax formula to keep only IDs of the last date of a month. (if a date is the last for the month, i keep all its rows)

My ouput will be the right table. I've many columns of course, i just displayed two for the POC.

SELECT *

FROM table t1

WHERE t1.date = (select max(t2.date)

from table t2

where month(t1.date) = month(t2.date) and year(t1.date) = year(t2.date)

)

This is the sql code i want 🙂  

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please check the formula:

Table 2 =
VAR tmp =
    SELECTCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date].[Year],
            'Table'[Date].[Month],
            "max_", MAX ( 'Table'[Date] )
        ),
        "max_", [max_]
    )
RETURN
    FILTER ( 'Table', 'Table'[Date] IN tmp )

Best Regards,

Jay

[Anonymous]

Hi Mounir

Try this in a new table.

FILTER (
    ADDCOLUMNS (
        SUMMARIZE (
            'ajout col',
            'ajout col'[Qui],
            'ajout col'[Jour],
            'ajout col'[Valeur],
            'Date'[Mois_Num]
        ),
        "@max",
            IF (
                'ajout col'[Jour]
                    = CALCULATE (
                        MAX ( 'ajout col'[Jour] ),
                        KEEPFILTERS ( 'Date'[Mois_Num] ),
                        ALL ( 'Date'[Date] ),
                        ALLEXCEPT ( 'ajout col', 'ajout col'[Qui] )
                    ),
                "Yes",
                "No"
            )
    ),
    [@max] = "Yes"
)

ajout col=Tabkle Name

qui=ID

jour=Date

Valeur= Amount

[Shishir22]

Hello @Anonymous ,

Use Below M code in advance editor in Power Query-

 #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName(Date.From([Date])), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"ID", "Month Name", "Year"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"ALLRows", each _, type table [Date=nullable date, ID=nullable text, Amount=nullable number, Month Name=text, Year=number]}}),
    #"Expanded ALLRows" = Table.ExpandTableColumn(#"Grouped Rows", "ALLRows", {"Date", "Amount"}, {"Date", "Amount"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ALLRows", each [Date] = [MaxDate]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate"})
in
    #"Removed Columns"

Refer .PBIX file from here-

max date rows of each month and year.pbix

Please mark it as answer if it solves your issue. Kudos are also appreciated.

[Anonymous]

it's not exactly what i want, and i've a lot of columns to manage, this was just a small example.

I'm looking for a DAX formula to select exactly what i want.

Thanks for your effort

[Shishir22]

Hello @Anonymous ,

Create a calculated column using DAX as below-  

Flag = 
VAR _MaxDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[Month] = EARLIER ( 'Table'[Month] )
        )
    )
RETURN
    IF ( _MaxDate = 'Table'[Date], 1, BLANK () )

And use this at report/visual level as FLAG =1 

Please mark it as answer if it resolves your issue. Kudos are also appreciated.