# Most Recent Date for Each Region

I have a table which contains monthly metrics data for six different metrics across four regions. I want to be able to display the latest values for each metric for each region in a table. The totals row shouId have the average value of all items displayed in each column. feel like it should be easy, but the solution is eluding me this afternoon.

NOTE:

• Region 3 only has dates for Feb, so it should show Feb values in the table.
• All other regions have dates for both Feb and March, so they should show only the March values in the table

I have included a PBIX file showing the data and a couple of things Iâ€™ve tried so far, but all Iâ€™m getting so far is the average of all values for each region/metric.

Thanks in advance for the help. Sorry if my description of the issue is not sufficiently detailed.

eDNA Demo 4.pbix (85.2 KB)

If youâ€™ve got other solutions, Iâ€™d love to hear them, but I think I was able to achieve what I was after by changing the [As Of Date] measure as follows. I canâ€™t claim to completely understand it, but it works.

``````As Of Date (old) =
STARTOFMONTH(
LASTDATE(Dates[Date])
)

As Of Date (new) =
CALCULATE(
STARTOFMONTH(
LASTDATE(Data[Date])
),
ALLSELECTED()
)``````

Hello @DaveC,

Thank You for posting your query onto the Forum.

Well, I tried both of the formulas that youâ€™ve provided to achieve the â€śMost Recent Date For Each Regionâ€ť. And itâ€™s providing a bit vague results in terms of dates for each region. Below is the screenshot of the result provided for the reference -

As per â€śAs of Date (Old)â€ť measure, itâ€™s providing the date result as â€ś01-12-2021â€ť whereas â€śAs of Date (New)â€ť measure itâ€™s providing all the date results as â€ś01-03-2021â€ť this would have been correct had all the regions had the same date. But for â€śRegion 3â€ť youâ€™ve the end date as â€ś01-02-2021â€ť so not sure how itâ€™s evaulating the correct results in your file. If you select â€śRegion 3â€ť in a slicer than â€śyesâ€ť it does evaluate to correct result. Below is the screenshot of the result provided for the reference -

If you remove the slicer selection than all the results are evaluated to â€ś01-03-2021â€ť which is not appropriate.

Rather than writing long and complicated formulas to achieve the â€śMost Recent Date For Each Regionâ€ť you can write one small and short formula as provided below -

``````As of Date - Harsh =
MAXX( Data , Data[Date] )
``````

Now, in the above screenshot youâ€™ll observe that although I havenâ€™t made any slicer selection the formula is evaluating correct results for the â€śRegion 3â€ť which has end date as â€ś01-02-2021â€ť rather than â€ś01-03-2021â€ť.

Lastly, to calculate the â€śTotal Valueâ€ť as per â€śMost Recent Date For Each Regionâ€ť. You can use the below provided formula -

``````Total Value of Recent Month =
CALCULATE( SUM( Data[Value] ) ,
FILTER( ALL( Dates ) ,
Dates[Date] = MAXX( Data , Data[Date] ) ) )
``````

And than write the measure to correct the â€śGrand Totalâ€™sâ€ť -

``````Correct Totals =
SUMX(
SUMMARIZE(
Data ,
Data[Region] ,
Data[Metric] ,
"@Totals" ,
[Total Value of Recent Month] ) ,
[@Totals]
)
``````

Iâ€™m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that youâ€™ve been looking for.

Note: To review the measures that Iâ€™ve created go to the â€śKey Measures - Harshâ€ť folder.

Thanks and Warm Regards,
Harsh

1 Like

Hi @DaveC, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @DaveC, weâ€™ve noticed that no response has been received from you since the 12th of March. We just want to check if you still need further help with this post? In case there wonâ€™t be any activity on it in the next few days, weâ€™ll be tagging this post as Solved.

Hi @DaveC, a response on this post has been tagged as â€śSolutionâ€ť. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Weâ€™ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

@Harsh, Thank you for the help! Iâ€™m not sure why my solution seems to be working fine in the more complex data model that I have in my actual use case, but I will definitely study yours and see how I can apply it to mine.

Thanks again!

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:

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!

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