Running Total For Runs per year

[iabramson]

How do I create a calculated text column in my data set of running total for the year based on run type and year. Basically looking for a column that is a unique identifier to use in a relationship with another data set where the first two digits are the last digits of the year (23/24), the middle is the type of run (ABC), and the last digit is the count of the number of runs per year for A, B , or C which resets every year. For example, on

1/2/2023 (23A1),

2/5/2023 (23B1),

3/5/3023 (23B2),

6/5/2024 (23B3)

12/30/2023 (23A2)

1/3/2024 (24A1)

1/5/2024 (24A2)

2/15/2024 (24A3)

4/15/2024 (24B1)

4/202/204 (24C1)

 Etc....

[ryan_mayu]

@iabramson 

you can try this to create a column

Column =

var _count=countx(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])&&year('Table'[Date])=year(EARLIER('Table'[Date]))&&'Table'[Run Type]=EARLIER('Table'[Run Type])),'Table'[Date])

return right(year('Table'[Date]),2)&'Table'[Run Type]&_count

[iabramson]

That worked thank you, now how would i link this to a second data set / table of "future runs" (with a relationship by date between both tables ) that would continue to build the run total count off the previous past count ?

Date	Run	desired result
12/21/2024	A	24A04
12/23/2024	C	24C02
12/25/2024	C	24C03
12/30/2024	B	24B02

[ryan_mayu]

why the first date is A04? I think in your original table, we already have 24A07, why the date is not 24A08?

[iabramson]

Sorry I changed the data to line up with the screen shot you sent. Ether way trying to get the count to continue going in a second dataset of future scheduled or planned runs if that makes sense ?

[ryan_mayu · Accepted solution]

@iabramson 

You don't have the same date in both tables, so you don't need to create relationship between tables. 

you can try this

Column =

var _count1=countx(FILTER('Future runs','Future runs'[Date]<=EARLIER('Future runs'[Date])&&'Future runs'[Run]=EARLIER('Future runs'[Run])&&year('Future runs'[Date])=year(EARLIER('Future runs'[Date]))),'Future runs'[Date])

var _count2=countx(FILTER('Table','Table'[Date]<='Future runs'[Date]&&'Table'[Run Type]='Future runs'[Run]&&year('Table'[Date])=year('Future runs'[Date])),'Table'[Date])

return right(year('Future runs'[Date]),2)&'Future runs'[Run]&right("0"&(_count1+_count2),2)

pls see the attachment below

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result very clearly.

[iabramson]

Date	Run Type	Desired Result
1/2/2023	A	23A01
2/5/2023	B	23B01
3/5/2023	B	23B02
6/5/2023	B	23B03
8/1/2023	B	23B04
12/30/2023	A	23A02
1/3/2024	A	24A01
1/5/2024	A	24A02
2/15/2024	A	24A03
3/1/2024	A	24A04
4/15/2024	B	24B01
4/20/2024	C	24C01
5/1/2024	C	24C02
5/15/2024	B	24B02
6/1/2024	A	24A05
7/1/2024	B	24B03
8/1/2024	C	24C03
9/1/2024	A	24A06
12/1/2024	B	24B04
12/20/2024	A	24A07

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9LDsAgCATQu7CWBPDTdtn2GMb7X6OaqhHZvsw4mDOQIDEKiQcHNxRXKWLVTs8kbyltxEinJk/Isj3v+2JQixvxOGJJcT9Cp8Kgf1FooXcWo6ZWjLrYUskuHjZ12ucvU2Qxd/H6ofIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Run Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Run Type", "Year"}, {{"Count", each Table.AddRankColumn(_,"Index","Date")}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index"}, {"Date", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each Text.End(Text.From([Year]),2)&[Run Type]&"0"&Text.From([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

Hope this helps.

[iabramson]

The above table is "past or closed orders/runs" and am looking so I can use this with a second data set where these will be added/ componded to "future open runs/orders" scheduled in the future.. IE see below

Future orders 2nd data set
Date	Run	desired result
12/21/2024	A	24A08
12/23/2024	C	24C04
12/25/2024	C	24C05
12/30/2024	B	24B05

[v-junyant-msft]

Hi @iabramson ,

Thanks for ryan_mayu's reply!
And @iabramson , you can try this:

Desired Result = 
VAR CurrentYear = YEAR([Date])
VAR YearPrefix = RIGHT(CurrentYear, 2)
VAR RunType = [Run]
VAR MaxRunningNumberFuture = 
    CALCULATE(
        COUNTROWS('future open runs/orders'),
        FILTER(
            'future open runs/orders',
            [Run] = EARLIER([Run]) &&
            YEAR([Date]) = YEAR(EARLIER([Date])) &&
            [Date] <= EARLIER([Date])
        )
    )
VAR MaxRunningNumberPast = 
    MAXX(
        FILTER(
            'past or closed orders/runs',
            [Run Type] = EARLIER([Run]) &&
            YEAR([Date]) = YEAR(EARLIER([Date]))
        ),
        'past or closed orders/runs'[Desired Result]
    )
VAR MaxRunningNumber =
    IF(
        MaxRunningNumberPast = BLANK(),
        0,
        VALUE(RIGHT(MaxRunningNumberPast, 2))
    )
VAR RunningNumber =
    MaxRunningNumber + MaxRunningNumberFuture
RETURN
YearPrefix & RunType & FORMAT(RunningNumber, "00")

Output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept both it and ryan_mayu's reply as the solution to help the other members find it more quickly.