Spread contract value across time

[Anonymous]

.

[Greg_Deckler · Accepted solution]

All right, good luck.

[Greg_Deckler]

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

[Greg_Deckler]

The quick measures I referred you to are measures, not columns.

[Greg_Deckler · Accepted solution]

All right, good luck.

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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!

[Anonymous]

One thing I don't understand.

In the billing example pbix 

There is a Calendar Table with Date column. It was showing me chronological order in the bar graph. When I removed it and put back again to the chronology changed (I think I was playing with my data sample of 5 ). 

Is there anything beside Date and Date Hierarchy to play around ?

Like in my ultimate version I got data for 2017,2018,2019,2020  but by choosing Hierarchy I can only see Jan-Dec 

Not sure whether it is 2019 which has all the amounts really or all combined by monthly basis. 

I would like to see continum of 2017-2020 

any idea ?

Thanks