Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a Contract table that includes the following columns:
ID | Account | Service | Start Date | End Date | Amount ($) |
1 | Account A | Service 1 | 2021/05/18 | 2022/05/17 | $100,000 |
2 | Account A | Service 1 | 2022/05/18 | 2023/05/17 | $105,000 |
3 | Account B | Service 1 | 2021/04/13 | 2022/04/12 | $80,000 |
4 | Account B | Service 2 | 2022/09/15 | 2022/09/14 | $50,000 |
Consider for this question, today's date is 2022/06/04 (June 4th, 2022). I'd like to create a new table using DAX that summarizes, for each Account and Service, the following:
The new table should look like this (using data from the original Contracts table above):
Account | Service | Action | Most Recent Contract ID | Renewal Rate (%) |
Account A | Service 1 | Renew | 2 | 105% |
Account B | Service 1 | Cancel | 3 | null |
Account B | Service 2 | New | 4 | null |
In the new table (above) I'd like:
I have NO idea where I'd start with this. I have some basic DAX knowledge so hopefully, with some input from the Power BI community, I can get most of the way there!
Solved! Go to Solution.
Hi @barend_dronkers
Here is a sample file with the solution https://www.dropbox.com/t/MHLqHgqhR8T8rNOc
Action =
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractStart = MAXX ( ContractsTable, Contracts[Start Date] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR Last2ndContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR ContractGap = DATEDIFF ( Last2ndContractEnd, LastContractStart, DAY )
VAR Result =
SWITCH (
TRUE ( ),
LatestContractEnd <= TODAY ( ), "Cancel",
ISBLANK ( Last2ndContractEnd ) || ContractGap > 1, "New",
"Renew"
)
RETURN
Result
Most Recent Contract ID = MAX ( Contracts[ID] )
Renewal Rate (%) =
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractValue = MAXX ( FILTER ( ContractsTable, Contracts[End Date] = LatestContractEnd ), Contracts[Amount ($)] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR PreviousContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR PreviousContractValue = MAXX ( FILTER ( PreviousContracts, Contracts[End Date] = PreviousContractEnd ), Contracts[Amount ($)] )
RETURN
IF (
[Action] = "Renew",
DIVIDE ( LastContractValue, PreviousContractValue)
)
Hello,
I made it with adding 2 columns in the first table.
My table Feuil 1
Code for Action Column :
Thank you!! It might actually make more sense to not create an extra table. I'm going to give this a try today. Also appreciate understanding your logic!
Hi James. I like that your solution uses columns, because it means I can use a slicer visual to filter on the type of action. One problem I'm running into with your solution is that _nbrows counts total number of rows, regardless of whether the previous contracts are actually earlier in time. Action should only flag something as "renew" if the previous contract's EndDate (if any exist) is adjacent (1 day less) than the StartDate of the contract we're flagging as a "renew".
Hi @barend_dronkers
Here is a sample file with the solution https://www.dropbox.com/t/MHLqHgqhR8T8rNOc
Action =
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractStart = MAXX ( ContractsTable, Contracts[Start Date] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR Last2ndContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR ContractGap = DATEDIFF ( Last2ndContractEnd, LastContractStart, DAY )
VAR Result =
SWITCH (
TRUE ( ),
LatestContractEnd <= TODAY ( ), "Cancel",
ISBLANK ( Last2ndContractEnd ) || ContractGap > 1, "New",
"Renew"
)
RETURN
Result
Most Recent Contract ID = MAX ( Contracts[ID] )
Renewal Rate (%) =
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractValue = MAXX ( FILTER ( ContractsTable, Contracts[End Date] = LatestContractEnd ), Contracts[Amount ($)] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR PreviousContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR PreviousContractValue = MAXX ( FILTER ( PreviousContracts, Contracts[End Date] = PreviousContractEnd ), Contracts[Amount ($)] )
RETURN
IF (
[Action] = "Renew",
DIVIDE ( LastContractValue, PreviousContractValue)
)
Thanks! In my actual data the Contract ID field is not actually a simple incrementing number. So would I be able to substitute an expression for Most Recent Contract ID that gives me the Contract ID for the contract with the most recent End Date?
I think I got it working with:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |