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