Forum Discussion
Spread contract value across time
.
All right, good luck.
You should actually thank Phil_Seamark for that little beauty, until I understood GENERATE (which I came to from reading his book), that problem vexed me from almost the very first day I worked with Power BI!
I did not manage with the solution I was following.
I used your code and it works !!!!
It just look so scary I was afraid to even read it :)
I have code below.
It uses SUMX at the end.
Total Amount 2 =
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Demand',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")),
"MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#")))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpBilling,
SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
),
[MonthYear] >= [MonthYearBegin] &&
[MonthYear] <= [MonthYearEnd]
),
"Customer",[Customer],
"Year",[Year],
"Month",[Month],
"Amount",[Amount]
)
RETURN SUMX(tmpTable,[Amount])You should actually thank Phil_Seamark for that little beauty, until I understood GENERATE (which I came to from reading his book), that problem vexed me from almost the very first day I worked with Power BI!