Calculating Annual Contract Value - start date end date

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]

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

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

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