Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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
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
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
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.
See if my Periodic Billing Quick Measure helps:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
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:(
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.
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]
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |