Most Recent Date for Each Region

DAX DAX Calculations
8

@DaveC,

Let me propose a totally different approach here that I think is much easier. The Power Query Group By function eats these sorts of grouping and aggregation problems for breakfast. Watch how simple this is:

Snag_341d3f6
Snag_341d3f61049×741 15.4 KB

image
image1254×453 27.6 KB

Then just create a column that subtracts Date from Max Date, and filter out any records where the difference is nonzero. Here’s the full M code:

let
    Source = Data,
    #"Grouped Rows" = Table.Group(Source, {"Region", "Metric"}, {{"All Data", each _, type table [Region=nullable text, Date=nullable date, Metric=nullable text, Value=nullable number]}, {"Max Date", each List.Max([Date]), type nullable date}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Date", "Value"}, {"Date", "Value"}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Expanded All Data", "Subtraction", each Duration.Days([Max Date] - [Date]), Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Date Subtraction", each ([Subtraction] = 0))
in
    #"Filtered Rows"

Now just drop the relevant fields from the resulting table into a matrix visual and boom!

image

You can use the approach in the following post to force your totals to be averages for either rows, columns or both

I hope this is helpful. Full solution file attached below.

FYI – I’ve become totally enamored of the power and flexibility of the Group By function. Actually have a video on this coming out on Monday showing some additional applications of it.

– Brian
EDNA Forum – Region Metric Solution.pbix (87.6 KB)

1 Like