cancel
Showing results for
Did you mean:
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
Community Support

hi,@ballist1x

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

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:

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

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.
Helper III

@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:(

Community Support

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.
Helper III

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.

Helper III

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.

Super User IV

See if my Periodic Billing Quick Measure helps:

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

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

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

Proud to be a Super User!

Helper III

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:(

Helper III

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

Helper III

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.

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]

Announcements