Forum Discussion
Solved
Cummulative value from startDate
Hello, I would like to calculate a monthly value and its accumulation from a defined start-date on and display it over time. My sample set is very simple but I am just not able to figure it out how...
Hi nexaframe
I delete the relationship between two tables, then you can refer to the three measures
ValuebyTime = var _startdate=LOOKUPVALUE('DataSet'[startdate],[ID],MAX('DataSet'[ID])) var _value=LOOKUPVALUE('DataSet'[value],'DataSet'[ID],MAX([ID])) return IF(MAX('Calendar'[YYYY-MM])>FORMAT(_startdate,"YYYY-MM"),_value,0) Sum_valuetime = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "v",[ValuebyTime]) var _cur_id = VALUES('DataSet'[ID]) var _cur_date = VALUES('Calendar'[YYYY-MM]) return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM]<=MAX('Calendar'[YYYY-MM])) , [v]) Actual_sum_output = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[YYYY-MM])) , "sums",[Sum_valuetime]) var _cur_id = VALUES('DataSet'[ID]) var _cur_date = VALUES('Calendar'[YYYY-MM]) return SUMX(FILTER(_t,[ID] in _cur_id&&[YYYY-MM] in _cur_date), [sums])Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I figured out the MonthlySavingsSinceImplemenationDate:
ValuebyTime=
CALCULATE(
SUM(DataSet[value]),
USERELATIONSHIP('Calendar'[Date],DataSet[startdate])MonthlySavingSinceImplementation=
CALCULATE(
[ValuebyTime],
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)