Forum Discussion

wardy912's avatar
wardy912
Icon for Super User rankSuper User
3 years ago
Solved

Add value to several months

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.

  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      wardy912
      Icon for Super User rankSuper User
      3 years ago

      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:


      CustomerDescriptionMonthly PaymentNumber of MonthsTotal ValueFirst Invoice Month
      Customer 1Short Term Contract£6,5002£13,000Nov-22
      Customer 2Managed Service Contract£10,00012£120,000Nov-22
      Customer 3Project  £100,000Dec-22
      Customer 4Short Term Contract£5,0005£25,000Feb-23
      Customer 5Managed Service Contract£20,0006£120,000Jan-23

       

      Required Result:

       

      MonthCustomer 1Customer 2Customer 3Customer 4Customer 5Monthly 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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