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
     
     

     

     

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

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's avatar
iabramson
Frequent Visitor
1 year ago

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 ?

DateRundesired result
12/21/2024A24A04
12/23/2024C24C02
12/25/2024C24C03
12/30/2024B24B02

 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

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

    • iabramson's avatar
      iabramson
      Frequent Visitor
      1 year ago

      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's avatar
        ryan_mayu
        Super User
        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