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 t...
  • 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.

     

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.