Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nexaframe
Frequent Visitor

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: 2023-02-09_15h15_49.png

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

 

vxinruzhumsft_0-1677050014468.png

 

 

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.

View solution in original post

7 REPLIES 7
v-xinruzhu-msft
Community Support
Community Support

Hi @nexaframe 

You can refer to the following measure.

Cummulative value=SUMX(FILTER(ALL('Calendar'),[Date]<=MAX('Calendar'[Date])),[ValuebyTime])

vxinruzhumsft_0-1676354032252.png

 

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.

 

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

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

vxinruzhumsft_0-1676361896612.png

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.

 

 

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

 

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

 

vxinruzhumsft_0-1677050014468.png

 

 

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.

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)
 

SALES_yd.JPGsol_1.JPG

sol3.JPG

Request you to please suggest .

 

Thanks,

Ashish

nexaframe
Frequent Visitor

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.