Forum Discussion

iabramson's avatar
iabramson
Frequent Visitor
1 year ago
Solved

Running Total For Runs per year

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 w...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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
     
     

     

     

iabramson's avatar
iabramson
Frequent Visitor
1 year ago
DateRun TypeDesired Result
1/2/2023A23A01
2/5/2023B23B01
3/5/2023B23B02
6/5/2023B23B03
8/1/2023B23B04
12/30/2023A23A02
1/3/2024A24A01
1/5/2024A24A02
2/15/2024A24A03
3/1/2024A24A04
4/15/2024B24B01
4/20/2024C24C01
5/1/2024C24C02
5/15/2024B24B02
6/1/2024A24A05
7/1/2024B24B03
8/1/2024C24C03
9/1/2024A24A06
12/1/2024B24B04
12/20/2024A24A07
iabramson's avatar
iabramson
Frequent Visitor
1 year ago

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
DateRundesired result
12/21/2024A24A08
12/23/2024C24C04
12/25/2024C24C05
12/30/2024B24B05

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.