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...
DAX
Running totals and cumulatives
  • 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.

lbendlin's avatar
lbendlin
Super User
1 year ago

Sales never catch up.

 

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Ibendlin,

Thank you for you comment.

 

I think you may have missunderstood what I'm trying to achieve. To summarise and simplify what I'm trying to achieve is,


I have a table with 4 columns:

  • Date (each row represents one week)
  • Contract (let ignore this column for now)
  • Sales
  • WIP

The table is sorted by Date Asc

 

whenever the WIP value is greater than 0 , starting from the same row, I want to sum the Sales column going back in time till the Sales value is greater or equal to the WIP in question. 

This needs to occur everytime WIP value is greater than 0.

 

I want the output to display how many weeks Sales took to equal or be greater than WIP 

 

when WIP = 7,147,211.20, it took Sales (Summing backwards in time)  6 weeks to >= to WIP

when WIP = 449,940, it took Sales (summing backwards in time) 1 week to >= to WIP

and so on... 


  • lbendlin's avatar
    lbendlin
    Super User
    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])
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks this worked to get the date of the FirstWeekToMeetTarget. However, instead of showing the date, I want to display how many weeks in INT it took to reach FirstWeekToMeetTarget 

      eg.

      1. when WIP = 7,147,211.20, it took Sales (Summing backwards in time)  6 weeks to >= to WIP  Output = 6

       

      2. when WIP = 449,940, it took Sales (summing backwards in time) 1 week to >= to WIP
      Output = 1

      Thanks for the guidance so far!

      • lbendlin's avatar
        lbendlin
        Super User
        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.