Forum Discussion
Reverse Running Total based On Another Column
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]) )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.
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])
)
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!
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.