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 to calculate it.

 

I linked the dataset to a calendar-table already and tried various ways to get the month since implementation in a dynamic way.

Another approach would be to have a measure to see "if calendar-date > implementation date, then savingsvalue, else 0".

 

Below is the DataSet and the 2 desired Measures i would like to create:

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.

7 Replies

  • nexaframe's avatar
    nexaframe
    Frequent Visitor
    3 years ago

    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)
    )
)

     

  • 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.