Hi,
we are creating a report about the contracts in PowerBI. We are measuring new, cancelled and active contracts. The measures are connected with datetables.
New and cancelled contracts are working with a datetable and the active contracts are working with an exrtra datetable. We had to do this step to 2 datetables because with only one timetablle the measures influenced each other and we got wrong values. But if there’s another possible solution with only one datetable, we would appreciate it to change the report.
Our formulas are:
active contracts = CALCULATE(COUNT(contracts[Customer]);FILTER(dw_contracts;contracts[Start Date]<LASTDATE(Quarter[End of Month])&&contracts[End Date]>FIRSTDATE(Quarter[End of Month])&&contracts[totalprice in Euro]>0))
Cancellation = CALCULATE(COUNT(contracts[Customer]);FILTER(contracts;dw_contracts[Annual Contract Value]>0);Filter(contracts;dw_contracts[End Date]<=LASTDATE(DateTable2[Date]));CALCULATETABLE(contracts;dw_contracts[End Date]);CALCULATETABLE(DateTable2;DateTable2[Date]))
new contracts = CALCULATE(COUNT(contracts[Account Number]);USERELATIONSHIP(contracts[Start Date];DateTable[Date]);FILTER(contracts;contracts[Annual Contract Value]>0&&contracts[Start Date]<=LASTDATE(DateTable[Date])))
So no we want to create the cancellation rate. The Cancellation Rate means that we measure the active contracts at a date and after a certain period we are measuring how many of these active contracts went cancelled. In this case the 2 datetales are the issue for creating this measure. Do you have any idea how we can create a measure for this?
Thank you for your efforts.
