Forum Discussion

New Member

6 years ago

Sum / divide values between dates

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 mea...

Community Champion

6 years ago

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
New Member
6 years ago
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?