Sum / divide values between dates

[Sarah_Jolliffe]

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.

[Carbon initiatives]

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

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

                                      "MonthYearEnd",VALUE(YEAR([End date])))

VAR tmpTable =

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpCarbon,

            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])

        ),

        [MonthYear] >= [MonthYearBegin] &&

        [MonthYear] <= [MonthYearEnd]

    ),

    "tCO2e",Carbon_Initiatives[Daily tCO2e Saved]

)

RETURN SUmx(tmpTable,[Total tCO2e])

[PBI model]

Year	tCO2e saved
2017	649927.39
2018	793721.03
2019	483959.38

Any help much appreciated

[Greg_Deckler]

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]

Spoiler

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

                                      "MonthYearEnd",VALUE(YEAR(Carbon_Initiatives[End date])))

VAR tmpTable =

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpcarbon,

            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])

        ),

        [MonthYear] >= [MonthYearBegin] &&

        [MonthYear] <= [MonthYearEnd]

    ),

    "CI_UID",[CI Ref number],

    "Year",[Year],

    "Month",[Month],

    "tCO2e",Carbon_Initiatives[Daily tCO2e Saved]

)

RETURN SUMX(tmpTable,[tCO2e])



and i now get these results

Year	tCO2e Saved
2018	629.48
2019	708.15
2020	1.35

should be this

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

Ideas?