cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rubio3003
Frequent Visitor

Last month of data available filter

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

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

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"

vkkfmsft_0-1627546101982.png


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.

View solution in original post

1 REPLY 1
v-kkf-msft
Community Support
Community Support

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"

vkkfmsft_0-1627546101982.png


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.

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors