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.

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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
        2 years ago

        Hi Anonymous ,

         

        I am trying to create similar kind of cummulative total with respect to today's date however i couple of  scenario :

        1) need the cummulative total of Qunatities if delivery date <= Today .
        2) if Delivery date > today then it gove the qunatoty which is available in the column.

         

        I tried the below measre but it's not taking Date column however when i tried to create a calculated column then it's working but i am getting sum of whole column(Quantity). Request you to please help is there any other way to achieve this .

         

        Please see below futher:

        Column 2 =
        VAR beforeToday =
        CALCULATE(
            [Sum_of_MG01],
            FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
            ZIBP_PO_MGRTN[DELIVERY_DATE] <= MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))

         

        VAR afterToday =
        CALCULATE(
            [Sum_of_MG01],
            FILTER(ALLSELECTED(ZIBP_PO_MGRTN),
            ZIBP_PO_MGRTN[DELIVERY_DATE] > MAX(ZIBP_PO_MGRTN[DELIVERY_DATE])))



        RETURN
        IF(ZIBP_PO_MGRTN[DELIVERY_DATE] <= TODAY(), beforeToday, afterToday)
         



        Request you to please suggest .

         

        Thanks,

        Ashish