Last month of data available filter

[rubio3003]

Good morning community, 

I need to display the last month of data available for a dashboard. It will be filtered by different facilities that submit data retrospectivelly. Some facilities have data all the way to June while others have lags all the way to June of last year (last month of data available varies by facility).

I need to filter the dashboard for whatever their last month of data is. I've tried lastmonth but I cannot make it work. 

Thank you in advance for your guidance and help.

Estefania

[v-kkf-msft · Accepted solution]

Hi @rubio3003 ,

You can try filter the last month of data available for per facility in Power Query Editor. Then if the data is updated, the tiles in the dashboard will also be updated. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdK7DcMwAAPRXVQbsEhbslwmaxjef40UQT7itVc9ELyu8ihLaavH6mqVe/mWM8tWUTSXviI4w5Zhz9Ay9AxHhpHhBKyiEA+soBW4glcAC2KBLJgNs7kvzIbZMBtmw2yY8yQdJ+nTSZ6/S9S/4Axbhj1Dy9AzHBlGhjPD+xJTgVXAClqBK3gFsCAWyILZH/P9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [facilities = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"facilities", type text}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"facilities"}, {{"Count", each List.Max([date]), type nullable date}, {"all", each _, type table [facilities=nullable text, date=nullable date]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"date"}, {"all.date"}),
    #"Filter Rows" = Table.SelectRows(#"Expanded all",each Date.Month([all.date]) = Date.Month([Count]) and Date.Year([all.date]) = Date.Year([Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Filter Rows",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"all.date", "date"}})
in
    #"Renamed Columns"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.