Have matrix using year/month rows summarize values as the most recent

[Anonymous]

I would like my matrix to summarize values as Most Recent as opposed to First/Last/Max

I have a number of Facilities that have monthly reports, part of those reports lists the current operator. I would like to have my Matrix show the most recent operator at each level in the hierarchy:

an example facility:

when I expand to years:

So already, I would expect the overall summarization to be showing Westdrum, not Husky

This is shown again when I expand the years that the switches of ownership occured:

2017 should show Trident, and 2020 should show Westdrum, which is not the case.

Something to note is that there isn't only a single entry per facility per month, but there could be 30, although in a given month they should all be the same operator, so it doesn't particularly matter.

I would like to find some sort of measure that could get this table to work the way I intend, as I also need a similar set up for a few other matrixes so this would be immensely helpful

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[Anonymous]

I am unable to share the full PBI file due to confidential information located in other parts of the dataset. This is really the best I can do.
Here is the 2020 data for the example I showed, with some information removed for safety.

Year	Month	OperatorName	ReportingFacilityID	ReportingFacilityProvinceState	ReportingFacilityType	ReportingFacilityIdentifier
2020	January	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	January	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	January	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	February	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	February	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	February	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	March	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	March	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	March	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	April	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	April	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	April	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	May	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	May	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	May	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	June	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	June	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	June	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	July	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	July	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	July	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	August	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	September	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	October	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	January	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	February	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	March	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	April	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	May	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	June	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	July	TRIDENT EXPLORATION (ALBERTA) CORP.	ABBT0041712	AB	BT	41712
2020	August	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	September	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	October	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	November	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712
2020	December	WESTDRUM ENERGY LTD.	ABBT0041712	AB	BT	41712

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hello, I appreciate the help, but I have run into an issue with the solution you provided. I build the columns as you've done, and they all seem to be correct, no errors, but when I put them into my Matrix visual I get an error saying that it "Can't display the visual", here is a picture of the exact message. Any advice on how to solve this?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.