Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone, would anyone be able to help with a DAX calculation for the following?
I have a data set showing sales won, split into projects (1 time payments) and contracts (multiple payments across multiple months). I have a 'win date' column and a 'number of months' column.
Any idea how I can get the value of the contracts to show in each month, starting from the win month?
So need the £6,500 added to November and December.
Thanks in advance.
Solved! Go to Solution.
HI @wardy912,
You can create a calendar table and use its date field and raw table custom field to create a matrix, then you can write measure formula to use current date and customer to lookup records:
formula =
VAR cdate =
MAX ( 'Calendar'[Date] )
VAR invoiceDate =
CALCULATE ( MAX ( 'Table'[First Invoice Month] ), VALUES ( 'Table'[Customer] ) )
VAR mVolume =
CALCULATE (
MAX ( 'Table'[Number of Months] ) + 0,
VALUES ( 'Table'[Customer] )
)
VAR _startDate =
DATE ( YEAR ( invoiceDate ), MONTH ( invoiceDate ), 1 )
VAR _endDate =
DATE ( YEAR ( _startDate ), MONTH ( _startDate ) + MAX ( mVolume, 1 ), 1 )
RETURN
IF (
cdate >= _startDate
&& cdate < _endDate,
CALCULATE (
MAX ( 'Table'[Total Value] ) / MAX ( mVolume, 1 ),
VALUES ( 'Table'[Customer] )
)
)
Regards,
Xiaoxin Sheng
Hi @wardy912,
What type of effect did you want to display? Split the 'multiple payments' records to multiple rows with divided values?
You can use the category fields and calendar date fields to create chart, then write a measure formula to use current category value to lookup raw table records if current date are include in the date range(start date + payment month offset) and show the divided results.
In addition, can you please share some dummy data with expected results? I think they will be help us clarify your data structure and test to coding formula:
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
Thank you for the quick response.
I have added dummy data with required results as requested.
I need to be able to show monthly total revenue from projects and contracts.
The monthly fee of contracts needs to be added to each month required, starting from the first invoice date, continuing accoring to the 'number of months' column.
Dummy Data:
Customer | Description | Monthly Payment | Number of Months | Total Value | First Invoice Month |
Customer 1 | Short Term Contract | £6,500 | 2 | £13,000 | Nov-22 |
Customer 2 | Managed Service Contract | £10,000 | 12 | £120,000 | Nov-22 |
Customer 3 | Project | £100,000 | Dec-22 | ||
Customer 4 | Short Term Contract | £5,000 | 5 | £25,000 | Feb-23 |
Customer 5 | Managed Service Contract | £20,000 | 6 | £120,000 | Jan-23 |
Required Result:
Month | Customer 1 | Customer 2 | Customer 3 | Customer 4 | Customer 5 | Monthly Total |
Nov-22 | £6,500 | £10,000 | £16,500 | |||
Dec-22 | £6,500 | £10,000 | £100,000 | £116,500 | ||
Jan-23 | £10,000 | £20,000 | £30,000 | |||
Feb-23 | £10,000 | £5,000 | £20,000 | £35,000 | ||
Mar-23 | £10,000 | £5,000 | £20,000 | £35,000 | ||
Apr-23 | £10,000 | £5,000 | £20,000 | £35,000 | ||
May-23 | £10,000 | £5,000 | £20,000 | £35,000 | ||
Jun-23 | £10,000 | £5,000 | £20,000 | £35,000 | ||
Jul-23 | £10,000 | £10,000 | ||||
Aug-23 | £10,000 | £10,000 | ||||
Sep-23 | £10,000 | £10,000 | ||||
Oct-23 | £10,000 | £10,000 |
Data can be downloaded from this link
Thank you.
HI @wardy912,
You can create a calendar table and use its date field and raw table custom field to create a matrix, then you can write measure formula to use current date and customer to lookup records:
formula =
VAR cdate =
MAX ( 'Calendar'[Date] )
VAR invoiceDate =
CALCULATE ( MAX ( 'Table'[First Invoice Month] ), VALUES ( 'Table'[Customer] ) )
VAR mVolume =
CALCULATE (
MAX ( 'Table'[Number of Months] ) + 0,
VALUES ( 'Table'[Customer] )
)
VAR _startDate =
DATE ( YEAR ( invoiceDate ), MONTH ( invoiceDate ), 1 )
VAR _endDate =
DATE ( YEAR ( _startDate ), MONTH ( _startDate ) + MAX ( mVolume, 1 ), 1 )
RETURN
IF (
cdate >= _startDate
&& cdate < _endDate,
CALCULATE (
MAX ( 'Table'[Total Value] ) / MAX ( mVolume, 1 ),
VALUES ( 'Table'[Customer] )
)
)
Regards,
Xiaoxin Sheng
That works perfectly, thank you!