Forum Discussion

bob57's avatar
bob57
Helper IV
6 years ago
Solved

Create Calculated Table

Source data table:

Rows 2 and 4 indicate multiple payments. B Corp to be paid $150 on 1/10, 2/10, and 3/10. D Corp to be paid $200 on 1/18 and 2/18.  I'm looking for a DAX formula to produce the following calculated table from the source table above:

Thank you for your time and assistance.

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi bob57 

     

    You can create a table like below.

    Table 2 = 
SELECTCOLUMNS(
    GENERATE(
        'Table',
        VAR __pumnts = 'Table'[No Pymnts]
        RETURN 
            GENERATESERIES( 1, __pumnts, 1 )
    ),
    "Date", 
        VAR __value = [Value] -1 
        VAR __date = 'Table'[Date] 
        VAR __year = YEAR( __date )
        VAR __month = MONTH( __date ) + __value
        VAR __day = DAY( __date )
        RETURN 
            DATE( __year, __month, __day ),
    "Payment", 'Table'[Payment],
    "Vendor", 'Table'[Vendor]
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi bob57 

     

    You can create a table like below.

    Table 2 = 
SELECTCOLUMNS(
    GENERATE(
        'Table',
        VAR __pumnts = 'Table'[No Pymnts]
        RETURN 
            GENERATESERIES( 1, __pumnts, 1 )
    ),
    "Date", 
        VAR __value = [Value] -1 
        VAR __date = 'Table'[Date] 
        VAR __year = YEAR( __date )
        VAR __month = MONTH( __date ) + __value
        VAR __day = DAY( __date )
        RETURN 
            DATE( __year, __month, __day ),
    "Payment", 'Table'[Payment],
    "Vendor", 'Table'[Vendor]
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

    • bob57's avatar
      bob57
      Helper IV
      6 years ago

      Worked perfectly! Thank you. Now Ineed to learn how it works. Appreciate your effort.