Forum Discussion
Group By - Filter by latest date within weekending
Hi all,
Pretty new to PowerBi so apologies for the basic question.
I've been using Group By but it's no longer achieving what I need and want to find a better way to group values. I want to group by week ending, item, and then the latest result in each phase. Someone had recommended using aggregation and the max operation on the phases to achieve the last results but as you can see from the example below H3 is 4 and H5 is 1 so it would return a value of 4 when in actuality at weeks ending the last value recorded was 1.
I messed around with it a bit and tried the max value on the date and then the aggregate groupings though it still results in the incorrect result.
Hi dd_au ,
You're correct, using MAX aggregators in the Group By isn't going to help you here - you need to group to a nested table then select the row of data which has the MAX of [date].
Try this:
1) Group By [week ending] and [item]. For the aggregate column, choose the 'All Rows' operator and call this column 'data'.
2) Now you have your nested table column, add a new custom column like this:
Table.Max([data], "date")You will now have a nested Record column that contains the full row of data for the max [date] value.
3) Expand the nested Record column, choosing whichever columns you want to reinstate back to the table.
Pete
2 Replies
Hi dd_au ,
You're correct, using MAX aggregators in the Group By isn't going to help you here - you need to group to a nested table then select the row of data which has the MAX of [date].
Try this:
1) Group By [week ending] and [item]. For the aggregate column, choose the 'All Rows' operator and call this column 'data'.
2) Now you have your nested table column, add a new custom column like this:
Table.Max([data], "date")You will now have a nested Record column that contains the full row of data for the max [date] value.
3) Expand the nested Record column, choosing whichever columns you want to reinstate back to the table.
Pete
Thanks Pete! This worked a treat. This will be pretty handy in the future.
Cheers,
DD
