topic Re: Add value to several months in DAX Commands and Tips

Add value to several months

wardy912 — Tue, 22 Nov 2022 09:47:51 GMT

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.

Re: Add value to several months

Anonymous — Wed, 23 Nov 2022 03:05:58 GMT

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

Re: Add value to several months

wardy912 — Wed, 23 Nov 2022 10:30:57 GMT

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.

Re: Add value to several months

Anonymous — Fri, 25 Nov 2022 07:11:35 GMT

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

Re: Add value to several months

wardy912 — Mon, 28 Nov 2022 08:55:42 GMT

That works perfectly, thank you!