Get last modification of an item by id per month

[macaruso2]

get last modification of an item by id
Hi, I have a list of tickets from a tool and I have a modification table, every time an analyst makes a modification a new record appears, but for my report I need to obtain only the last modification of an id for each month

the id is on the column displayid and the modification is on modificationtime column

i need to represent the last modificationtime of the displayid per month

Thanks and best regards.

[v-eqin-msft · Accepted solution]

Hi @macaruso2 ,

According to my understand, you want to calculate the latest datetime of each id for each month , right?

I did it in two ways.

1. In Power Query,please follow these steps: Add Date only column --> Add YearMonth column --> Group By 

YearMonth=Date.ToText([Date],"yyyy-MM")

2. Use DAX:

YearMonth=
YEAR ( 'Table'[modificationtime] ) & "-"
    & MONTH ( 'Table'[modificationtime] )

Measure =
RANKX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[displayid] = MAX ( 'Table'[displayid] )
            && 'Table'[YearMonth] = MAX ( 'Table'[YearMonth] )
    ),
    CALCULATE ( MAX ( ( 'Table'[modificationtime] ) ) ),
    ,
    DESC
)

Then apply the measure to filter as shown below:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[BA_Pete]

Hi @macaruso2 ,

My solution wasn't really intended to be applied directly to your source, as your field names etc. will be different from my example. It was intended to show you the steps that need to be taken to achieve what you wanted.

If you follow these steps on your source (assuming it is the same as your example picture) then you should get what you need:

1) Add a custom column called [monthYear] using the following formula:

Text.Combine({Text.Start(Date.MonthName([modificationtime]), 3), Text.End(Text.From(Date.Year([modificationtime])),2)}, " ")

2) Multi-select (hold down Ctrl while clicking column header) [displayid] and [monthYear].

3) On the Home tab of the ribbon, select 'Group By'.

4) In the bottom section of the dialog box, set the following in the three input boxes:

- New column name = maxDateTime

- Operation = Max

- Column = [modificationtime]

5) Hit OK, and you should have a table that looks like this:

Pete

[BA_Pete]

Hi @macaruso2 ,

You can do this in Power Query as follows. In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xCsAgEETRq4StFcYJicZTpEsh3v8awUJYFqZ+7J8dw76XloxnLsgEcaB1wGbyRE37qvbrCVQ0UZMLNh2MRE0uWHUwEjW54C2J+nnqrUXzBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [displayID = _t, time_stamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"displayID", type text}, {"time_stamp", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "month", each Text.Start(Date.MonthName([time_stamp]), 3), type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"displayID", "month"}, {{"maxDateTime", each List.Max([time_stamp]), type nullable datetime}})
in
    #"Grouped Rows"

When you perform the Group Rows step, you can multi-select as many fields as you want to be included and have the max Date Time of that combination found.

Pete 

[macaruso2]

Hello @BA_Pete

enter your code but I get an error, I don't have much experience with power query queries:

my original code is:

let
Origen = MySQL.Database("10.75.246.65", "securityreport", [ReturnSingleDatabase=true]),
securityreport_soc = Origen{[Schema="securityreport",Item="soc"]}[Data]
in
securityreport_soc

I add your code:

let
Origen = MySQL.Database("10.75.246.65", "securityreport", [ReturnSingleDatabase=true]),
securityreport_soc = Origen{[Schema="securityreport",Item="soc"]}[Data], Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [displayID = _t, time_stamp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"displayID", type text}, {"time_stamp", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "month", each Text.Start(Date.MonthName([time_stamp]), 3), type text),
#"Grouped Rows" = Table.Group(#"Added Custom", {"displayID", "month"}, {{"maxDateTime", each List.Max([time_stamp]), type nullable datetime}})
in
#"Grouped Rows"

in
securityreport_soc

I get the following error:

[macaruso2]

Hi @BA_Pete 

i add your code but have an error

my original code are:

let
Origen = MySQL.Database("10.75.246.65", "securityreport", [ReturnSingleDatabase=true]),
securityreport_soc = Origen{[Schema="securityreport",Item="soc"]}[Data]
in
securityreport_soc

I add your code:

let
Origen = MySQL.Database("10.75.246.65", "securityreport", [ReturnSingleDatabase=true]),
securityreport_soc = Origen{[Schema="securityreport",Item="soc"]}[Data], Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [displayID = _t, time_stamp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"displayID", type text}, {"time_stamp", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "month", each Text.Start(Date.MonthName([time_stamp]), 3), type text),
#"Grouped Rows" = Table.Group(#"Added Custom", {"displayID", "month"}, {{"maxDateTime", each List.Max([time_stamp]), type nullable datetime}})
in
#"Grouped Rows"

in
securityreport_soc

but have an error: "se esperaba el token Equal" in English "Equal token was expected"

you think I did a step incorrectly, I don't have much experience with queries