Filtering data only last week of the month

[Mars3442]

Hi All,

I have stock data

Date	Stock
2 Jan 2023	1000
1 Feb 2023	2000
1 March 2023	4000
10 April 2023	300
17 April 2023	200
24 April 2023	400
31 April 2023	500
14 May 2023	400
22 May 2023	200
29 May 2023	500

But I only need 1 data per month. If 1 month only has 1 data, show it. But if 1 month has more than 1 data, show the last week's data of that month

Expected result

Date	Stock
2 Jan 2023	1000
1 Feb 2023	2000
1 March 2023	4000
31 April 2023	500
29 May 2023	500

Please help me find Dax in Power BI or I can filter the data in Power Query

[ThxAlot]

Both ways are easy enough,

DAX

PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLwSsxTMDIwMlbSUTI0MDBQitWJVjJUcEtNgokaIUR9E4uSM2DiJnBxAwXHgqLMHJiEMUzcHFXcCCpuZIIqbgIVN0YzxxRmjgnQ4kp01UZGyKJwsy2RRcEmxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Stock = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Stock", Int64.Type}}),

    Grouped = Table.FromRecords(Table.Group(#"Changed Type", "Date", {"grp", Table.Last}, 0, (x,y) => Byte.From(Date.Month(x)<>Date.Month(y)))[grp])
in
    Grouped

[vibhormittal93]

Hi @Mars3442 ,

This problem is solved in one of the past discussions here: 

You can either filter the data in power query or create a calculated column to filter only the desired rows. Unfortunately, there won't be any easy way to filter rows via DAX.

I would suggest to add a calculated column to your table as suggested by Shishir.

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

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

Regards
Vibhor