Identify which Month of Sales (first, second,.... 12th etc)

[mgrayTCB]

I am trying to add a colum to a table that identifiles which month (first, second, third, or fourth) the amount represents by ID. I am trying to return the Result column below so I can filter out IDs and sales from first and second months of sales (or whatever early activity thereis in earlist months of the IDs existance). 

ID	Month	Amount	Result
1	1/31/2024	224.00	4
1	12/31/2023	91.00	3
1	11/30/2023	86.00	2
1	10/31/2023	141.00	1
2	3/31/2023	258.00	1
2	4/30/2023	52.00	2
2	5/31/2023	243.00	3
3	12/31/2023	104.00	1
3	1/31/2024	172.00	2
4	2/28/2023	193.00	4
4	1/31/2023	232.00	3
4	12/31/2022	136.00	2
4	11/30/2022	119.00	1

I am not even sure where to start with this. Any help would be appreciated.

[sayaliredij]

Hi @mgrayTCB 

Please use following formula for new column

Ranking = RANKX(FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[ID] = EARLIER ( 'Table (2)'[ID] ) ),'Table (2)'[Month],,ASC,Dense)

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[wini_R]

Hi @mgrayTCB,

You can check if the following query helps with your task. Just paste the code in the advanced editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdG9DsIgEAfwVzHMjdwXlY4mLg4+AenuYrvU9/c0LeUa8BYg98ufr5Tc/eY695in5anj9TW/p8WNXXKoS/SMnoBE56e9iOQMsCtaGVumNaCBmgd1GHsDoZ6IkvNIO1xXFGKppLlpoNKFRprwpvjPZRHEsPrL4SVv+e2Qp1jLGrhU2DgYm6x8MDqEcW/Y9gdHhsOPjR8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"Month", type date}}, "en-US"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Amount", type number}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"tab3", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "tab4", each Table.AddIndexColumn(
  Table.Sort([tab3],{{"Month", Order.Ascending}}),
  "result", 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"tab4"}),
    #"Expanded tab4" = Table.ExpandTableColumn(#"Removed Other Columns", "tab4", {"ID", "Month", "Amount", "result"}, {"ID", "Month", "Amount", "result"})
in
    #"Expanded tab4"

 Outcome:

[mgrayTCB]

Thank you. Is there a way to do something like that using add column in DAX?

[mgrayTCB]

I was thinking a rank of dates for each ID but I am not sure how to do it.

[wini_R]

An alternative using RANK function:

resultDAX = 
RANK(
    DENSE,
    ORDERBY(tab2[ID], ASC, tab2[Month], ASC),
    PARTITIONBY(tab2[ID])
)

[mgrayTCB]

This is helpful but my table is a bit more complicated than I orginally posted. I tried using

 DISTINCT(SELECTCOLUMNS(fact,"_id",fact[Property Id],"_mo",fact[Month])) to create a virtual table of unique combinations of ID and Month to then run the rank logic on but I ended up with all 1s.

Below is an excerpt of the table. It is financal income statement data for over 150 property IDs over 5 years (at monthly resolution). each recored represents a line in the income statement for a property in a month for a particular line for either budget or actual. All I care about is knowing if it is the first, second, third, etc month of operations so that I can filter out the early months and get a better representation of stabilized operations.

fact[Property Id]	fact[Account Id]	fact[Month]	fact[Amount Type]	fact[Amount]
147	683	2/28/2024	Budget	156
147	683	1/31/2024	Budget	111
147	683	12/31/2023	Budget	197
147	683	11/30/2023	Budget	173
147	683	10/31/2023	Budget	112
147	683	9/30/2023	Budget	123
483	683	2/28/2024	Budget	180
483	683	1/31/2024	Budget	153
483	683	12/31/2023	Budget	166
483	683	11/30/2023	Budget	192
483	683	10/31/2023	Budget	104
483	683	9/30/2023	Budget	176
483	683	8/31/2023	Budget	110
483	683	7/31/2023	Budget	187
147	683	2/28/2024	Actual	169
147	683	1/31/2024	Actual	174
147	683	12/31/2023	Actual	111
147	683	11/30/2023	Actual	118
147	683	10/31/2023	Actual	197
147	683	9/30/2023	Actual	167
483	683	2/28/2024	Actual	135
483	683	1/31/2024	Actual	102
483	683	12/31/2023	Actual	187
483	683	11/30/2023	Actual	109
483	683	10/31/2023	Actual	141
483	683	9/30/2023	Actual	127
483	683	8/31/2023	Actual	102
483	683	7/31/2023	Actual	130

[wini_R]

Can you please also include an extra column with expected result? I'm a bit confused now what we need to get here 😉

[mgrayTCB]

See below with an expected result column. Regardless of whether the record is a budget item or an actual item I just want to know for each property ID which ordered month the data represents. 

The oldest date for a particular property ID is considered its first month of operations.

fact[Property Id]	fact[Account Id]	fact[Month]	fact[Amount Type]	fact[Amount]	Expected Result (ordered month of operations)
147	683	2/28/2024	Budget	195	6
147	683	1/31/2024	Budget	168	5
147	683	12/31/2023	Budget	169	4
147	683	11/30/2023	Budget	169	3
147	683	10/31/2023	Budget	172	2
147	683	9/30/2023	Budget	165	1
483	683	2/28/2024	Budget	168	8
483	683	1/31/2024	Budget	173	7
483	683	12/31/2023	Budget	119	6
483	683	11/30/2023	Budget	160	5
483	683	10/31/2023	Budget	103	4
483	683	9/30/2023	Budget	147	3
483	683	8/31/2023	Budget	140	2
483	683	7/31/2023	Budget	173	1
147	683	2/28/2024	Actual	131	6
147	683	1/31/2024	Actual	123	5
147	683	12/31/2023	Actual	159	4
147	683	11/30/2023	Actual	172	3
147	683	10/31/2023	Actual	113	2
147	683	9/30/2023	Actual	193	1
483	683	2/28/2024	Actual	160	8
483	683	1/31/2024	Actual	138	7
483	683	12/31/2023	Actual	170	6
483	683	11/30/2023	Actual	146	5
483	683	10/31/2023	Actual	149	4
483	683	9/30/2023	Actual	183	3
483	683	8/31/2023	Actual	110	2
483	683	7/31/2023	Actual	165	1

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[fact[Property Id]]]=EARLIER(Data[fact[Property Id]]])&&Data[fact[Month]]]<=EARLIER(Data[fact[Month]]])&&Data[fact[Amount Type]]]=EARLIER(Data[fact[Amount Type]]])))

Hope this helps.

[wini_R]

OK, then both formulas provided before (RANKX and RANK) work fine: