Add value to several months

[wardy912]

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.

[v-shex-msft]

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

[wardy912]

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.

[v-shex-msft · Accepted 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

[wardy912]

That works perfectly, thank you!