Sum / divide values between dates

Question

Hoping someone can help. I've got stuck trying to do time-based analysis on the following table concerning carbon savings. An example of the table is below and also the closest DAX code used in a measure. The measure is not summing correctly.&nbsp;[Carbon initiatives]Initiaitve nameStart dateEnd DateDaily tCO2e SavedTotal tCO2e SavedCarbon 130/6/1831/12/200.00070990.6Carbon 217/7/1817/10/180.168129715.5Carbon 31/1/1731/12/192.23804042,448.4Carbon 422/2/1920/5/200.142583564.6&nbsp;[Measure]tCO2e saved = VAR tmpCalendar = ADDCOLUMNS('Datetable',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date])))VAR tmpCarbon = ADDCOLUMNS('Carbon_Initiatives',"MonthYearBegin",VALUE(YEAR([Start date])),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "MonthYearEnd",VALUE(YEAR([End date])))VAR tmpTable = SELECTCOLUMNS(&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GENERATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tmpCarbon,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &gt;= [MonthYearBegin] &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &lt;= [MonthYearEnd]&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; "tCO2e",Carbon_Initiatives[Daily tCO2e Saved])RETURN SUmx(tmpTable,[Total tCO2e])&nbsp;[PBI model]YeartCO2e saved2017649927.392018793721.032019483959.38&nbsp;Any help much appreciated

greg_deckler · Answer

Not 100% sure what you are going for but take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

sarah_jolliffe · Answer

Spoiler (Highlight to read)Cheers for the quick reply, I did come across those 2 posts and plagerised the code just inserted my table refs but that is chucking out duff figures. this is what i did;tCO2e saved = VAR tmpCalendar = ADDCOLUMNS('Dates2',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date])))VAR tmpcarbon = ADDCOLUMNS('Carbon_Initiatives',"MonthYearBegin",VALUE(YEAR(Carbon_Initiatives[Start date])),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "MonthYearEnd",VALUE(YEAR(Carbon_Initiatives[End date])))VAR tmpTable = SELECTCOLUMNS(&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GENERATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tmpcarbon,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &gt;= [MonthYearBegin] &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &lt;= [MonthYearEnd]&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; "CI_UID",[CI Ref number],&nbsp;&nbsp;&nbsp; "Year",[Year],&nbsp;&nbsp;&nbsp; "Month",[Month],&nbsp;&nbsp;&nbsp; "tCO2e",Carbon_Initiatives[Daily tCO2e Saved])RETURN SUMX(tmpTable,[tCO2e])and i now get these resultsYeartCO2e Saved2018629.482019708.1520201.35should be thisYeartCO2e Saved20183,45020194,8072020909Ideas?Cheers for the quick reply, I did come across those 2 posts and plagerised the code just inserted my table refs but that is chucking out duff figures. this is what i did;tCO2e saved = VAR tmpCalendar = ADDCOLUMNS('Dates2',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date])))VAR tmpcarbon = ADDCOLUMNS('Carbon_Initiatives',"MonthYearBegin",VALUE(YEAR(Carbon_Initiatives[Start date])),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "MonthYearEnd",VALUE(YEAR(Carbon_Initiatives[End date])))VAR tmpTable = SELECTCOLUMNS(&nbsp;&nbsp;&nbsp; FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; GENERATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tmpcarbon,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &gt;= [MonthYearBegin] &amp;&amp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [MonthYear] &lt;= [MonthYearEnd]&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp; "CI_UID",[CI Ref number],&nbsp;&nbsp;&nbsp; "Year",[Year],&nbsp;&nbsp;&nbsp; "Month",[Month],&nbsp;&nbsp;&nbsp; "tCO2e",Carbon_Initiatives[Daily tCO2e Saved])RETURN SUMX(tmpTable,[tCO2e])and i now get these resultsYeartCO2e Saved2018629.482019708.1520201.35should be thisYeartCO2e Saved20183,45020194,8072020909Ideas?&nbsp;

Forum Discussion

Sum / divide values between dates

2 Replies

Related content

divide 2 values

Divide by applicable values

Divide Column Values to New Columns

Divide in Power BI

Divide columns

Initiaitve name	Start date	End Date	Daily tCO2e Saved	Total tCO2e Saved
Carbon 1	30/6/18	31/12/20	0.0007099	0.6
Carbon 2	17/7/18	17/10/18	0.1681297	15.5
Carbon 3	1/1/17	31/12/19	2.2380404	2,448.4
Carbon 4	22/2/19	20/5/20	0.1425835	64.6

Year	tCO2e saved
2017	649927.39
2018	793721.03
2019	483959.38

Year	tCO2e Saved
2018	629.48
2019	708.15
2020	1.35

Year	tCO2e Saved
2018	3,450
2019	4,807
2020	909