Forum Discussion

nexaframe's avatar
nexaframe
Frequent Visitor
3 years ago
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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi nexaframe 

You can refer to the following measure.

Cummulative value=SUMX(FILTER(ALL('Calendar'),[Date]<=MAX('Calendar'[Date])),[ValuebyTime])

 

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.

 

  • nexaframe's avatar
    nexaframe
    Frequent Visitor
    3 years ago

    Hello Yolo,

    Thanks for your reply. I found the a similar measure also, but as it is showing in your screenshot, the totals are not correct.

    I need the column and row totals to be displayed correct, so I can report the process over time...

     

    I calculated the value like this:

     

    Cumm_ValuebyTime = 
CALCULATE(
	[ValuebyTime],
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
	)
)

     

     

    I think that I need to create some sort of  table that would show the summarized data in a expanded way, so i can see the running total over all IDs?

     

    I hope you could further support on that issue, as all my research and trials were dead ends...

    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi nexaframe 

      You can try the following measure

      Measure 3 = var _t =ADDCOLUMNS( CROSSJOIN(ALLSELECTED('DataSet'[ID]), ALLSELECTED('Calendar'[Date])) , "v",CALCULATE(SUMX(FILTER(ALLSELECTED('Calendar'),[Date]<=MAX('Calendar'[Date])),[ValuebyTime])))
var _cur_id = VALUES('DataSet'[ID])
var _cur_date = VALUES('Calendar'[Date])
return
SUMX(FILTER(_t,[ID] in _cur_id && [Date] in _cur_date) , [v])

      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.

       

       

      • nexaframe's avatar
        nexaframe
        Frequent Visitor
        3 years ago

        Hello,

        i really tried now to figure my the issues out - but I can't manage to come close to the result that i would like to see in your example.

         

        I uploaded the small pbix file to my google drive... hope you could help me out once more.

        cummulativeCalculationbyDate.pbix