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 

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