cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nick_88 Frequent Visitor
Frequent Visitor

Spread contract value across time

.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Spread contract value across time

All right, good luck.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Spread contract value across time

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!


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


8 REPLIES 8
Super User
Super User

Re: Spread contract value across time


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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nick_88 Frequent Visitor
Frequent Visitor

Re: Spread contract value across time

 
Super User
Super User

Re: Spread contract value across time

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nick_88 Frequent Visitor
Frequent Visitor

Re: Spread contract value across time

 
Super User
Super User

Re: Spread contract value across time

All right, good luck.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nick_88 Frequent Visitor
Frequent Visitor

Re: Spread contract value across time

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 Smiley Happy

 

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

Re: Spread contract value across time

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!


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


nick_88 Frequent Visitor
Frequent Visitor

Re: Spread contract value across time

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