Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Reverse Running Total based On Another Column

I've got 4 columns: date, ContractCode, SALES, WIP Date Contract Code SALES WIP 29/03/2024 600 1,543,706.16 10,209,064.12 05/04/2024 600 1,309,658.44 0.00 12/04/2024 60...
  • lbendlin's avatar
    lbendlin
    1 year ago

     

     

     

     

    FirstWeekToMeetTarget = 
    var w = max('Table'[WIP])
    return if(ISBLANK(w),BLANK(),
    var wk = max('Table'[Date])
    var a = CALCULATETABLE('Table',REMOVEFILTERS('Table'[SALES],'Table'[WIP]),'Table'[Date]<=wk)
    var b = ADDCOLUMNS(a,"cm",var wkd = [Date] return sumx(filter(a,[Date]>=wkd),[SALES]))
    var c = topn(1,filter(b,[cm]>=w),[Date],DESC)
    return CONCATENATEX(c,[Date])
    )

     

  • lbendlin's avatar
    lbendlin
    1 year ago

    You should be able to add that yourself. filter all week values to include the interval from the FirstWeekToMeetTarget to the current week, and return the count of rows.  Minus one if you don't want to count the current week.