Last Record of each month

[danishwahab]

Hello Everyone,

I have one record daily for each company showing number of Equipments installed as on that date. I want to create monthly table which will display last record for each company for each month as shown below in highlited instead of mutiple records.

Kindly guide.

Thanks!

[danishwahab]

Thanks everyone for your efforts and valuable inputs.

All of the solutions seems to be working in someway however as I wanted to resuse this table and wanted in the Transformation section I had accepted solution provide by @jgeddes . Thanks to @Omid_Motamedise for providing a link which helped me understand M Query logic. Thanks to @uzuntasgokberk for providing an alternative solution using a measure.

Regards!

[Omid_Motamedise]

See this video 

https://www.youtube.com/watch?v=QaodJFeX49k

[jgeddes · Accepted solution]

Here is a power query solution if you want to try it. Paste this example code into the advanced editor of a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9BCsAgDATAv+QsmGxVvLZ9hvj/b1gFE217C8OSTUqhkxyJePFghD4zVWcc1ePqME/Drz7C1oA31jhk+P2Ox40tnq119QObJ3Wx2p/rp0Md1svf/dPz9PC8VRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, #"As on Date" = _t, #"Equipment Counts" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"As on Date", type date}, {"Equipment Counts", Int64.Type}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.ToText([As on Date], [Format="yyyy-MM"]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Company", "Month"}, {{"filtered_table", each Table.SelectRows(_, (r)=>r[As on Date] = List.Max([As on Date])), type table [Company=nullable text, As on Date=nullable date, Equipment Counts=nullable number, Month=text]}}),
    #"Expanded filtered_table" = Table.ExpandTableColumn(#"Grouped Rows", "filtered_table", {"As on Date", "Equipment Counts"}, {"As on Date", "Equipment Counts"})
in
    #"Expanded filtered_table"

Basically you are creating a month-year column and then grouping on company and month-year. The aggregation is selecting the row that coresponds to the max date value for the company, month-year pair.

[uzuntasgokberk]

Hello @danishwahab ,

You find the solution below the images and dax code.

Cond(Create a measure) =
VAR finddate_=
CALCULATE(
MAX(Last_Case[Date]),
ALLEXCEPT(Last_Case,Last_Case[Company])
)
RETURN
IF(
SELECTEDVALUE(Last_Case[Date])=finddate_,
1,
0
)

Lastly filter the measure as 1 and you can remove the column the measure in the table because you filtered in filter pane.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[danishwahab]

Hi @uzuntasgokberk ,

Thanks for your quick response. This solution seem to be working to some extent may be I should have populated some more data of other months. As of now it is filtering single entry for a company where as I need single entry for a company in a month. 

E.g. A should come twice one in November and another in December.

Thanks!

[uzuntasgokberk]

Hello @danishwahab ,

Firstly, add a new column(calculated column) in your table that need to be YearMonth column. The dax code needs to be FORMAT(Table[date],"MM.YYYY"). After that use the measure below the image.

Cond1 =
SUMX(
SUMMARIZE(
FILTER(Last_Case, Last_Case[Date] = CALCULATE(MAX(Last_Case[Date]),ALLEXCEPT(Last_Case,Last_Case[Company],Last_Case[YearMonth]))),
Last_Case[Company],
Last_Case[Date],
Last_Case[YearMonth],
"Count",SUM(Last_Case[Count])
),
1
)

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.