Cummulative value from startDate

[nexaframe]

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:

[v-xinruzhu-msft]

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]

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

[v-xinruzhu-msft]

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]

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

[v-xinruzhu-msft · Accepted solution]

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.

[ashc_cool10]

Hi @v-xinruzhu-msft ,

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

[nexaframe]

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