cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballist1x
Helper III
Helper III

Calculating Annual Contract Value - start date end date

Hi i am tyring to calculate the annual value of Contracts for example the total to be billed in 2018, 2019 etc

 

Acontract has a

Start Date: Format dd/mm/yy

End Date: Format dd/mm.yy

total value £

Monthly value£

 

if a contract has a monthly value of £100 and goes from 1/1/2017 to 31/6/2018

 

i want to be able to select a drop down filter that i can select, for example, 2017 and it returns £1,200 (12x months * £100)

Also, if i select on the drop down 2018 it would return the value £600 (6 months * £100)

10 REPLIES 10
v-lili6-msft
Community Support
Community Support

hi,@ballist1x

   If your data like this, One Contract No has many Customer ID

1.PNG

Then you can use this formula

OpenContracts =
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"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]
    ),
    "Year",[Year],
    "Month",[Month],
    "OpenContracts",[Contract No.]
)
RETURN  CALCULATE(COUNTROWS(DISTINCT(tmpTable)))

Result:

2.PNG

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-lili6-msft
Community Support
Community Support

Hi@ ballist1x

After my research, I replicate your formula on my own report, it works well. For your error ,It should be wrong data type of your data.

Please check data type of column GBP Monthly Extended Price weather it is number.

If it is not your case, please share your pbix for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading

 

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lili6-msft

 

Hi Lin, yes it does work as long as the date fields are populated and there are no blanks in the date field data.

 

Now, the next question, is how do i get this to count the number of Active countracts?

 

i tried to amend the measure as below  but this doesnt work:(

 

i want a distinct count of the number of Contract No.s for each year that are activve in that date range.,

 

 

 

OpenContracts =
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"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],
    "OpenContracts",[Contract No.]
)
RETURN DISTINCTCOUNT(Billing[Contract No.])

 

Unfortunately this doesnt work:(

Hi@ ballist1x

You can try to use this measure as below

OpenContracts =
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Billing',"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],
    "OpenContracts",[Contract No.]
)
RETURN CALCULATE(COUNTROWS(tmpTable))

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft Lin, unforutnately that doesnt calculate correctly.

 

there are only 353,980 rows in total of the data set that i have and the distinct count of unique contract Numbers is 3614.

 

When i use the calculation that you provided it returns a number of 8 million in total, which is impossible.

Hi @Greg_Deckler and hi @v-lili6-msft

 

Also what would be really cool, would be, what has already been billed as of Today.

 

So i create da new date column in the billing data sheet.

 

its an iF statement, that of Today>Until date use until date, and if today<Until date use Today and then pointed the measure that that date column.

Greg_Deckler
Super User IV
Super User IV

See if my Periodic Billing Quick Measure helps:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Hi @Greg_Deckler

 

is there a way to add Day into this in addition to Month/Year?

 

reason being that unfortunately Date Diff can give me a number of 11 months for a contract that is actually 12 months so i might need to go down to ca;lculating this per Day, especially annoying when the day count is 364 days but datediff counts it as 11 months for instance:(

 

 

@Greg_Deckler

 

i got it working and it runs  charm!

 

the issue i had and could replicate on your PBIX was that if one of the rows of data has a blank calendar date, then the measure wont complete and will give an error.

 

 

Hi @Greg_Deckler

 

thats exactly what i need. And although i see it working on your PBIX for some reason i get an error when i replicate it on my own.

 

Couldnt load the visual...

 

NdxScript Model (11,55) calculation Error in Measure [TOTAL AMOUNT]: Cannot convert Value " of type Text to type Number.

 

 

This is my formula;

 

Total Amount =
VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS('Query1',"MonthYearBegin",VALUE(YEAR([Line Invoice Coverage Start]) & FORMAT(MONTH([Line Invoice Coverage Start]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([Line invoice Coverage End]) & FORMAT(MONTH([Line invoice Coverage End]),"0#")))
VAR tmpTable =
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",[Customer Name],
    "Year",[Year],
    "Month",[Month],
    "AMOUNT",[GBP Monthly Extended Price]
)
RETURN SUMX(tmpTable,[AMOUNT])

 

my fields in the table are:

 

Calendar[Date]

Query1[GBP Monthly Extended Price]

QUERY1[Customer Name]

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.