Adding additional rows to a calculated table

[PamWren]

Hi, 

I have created a calculated table using select Selectcolumns.  I want to add addtional rows to this for each month between the start and end date, with the monthly payment populatated for that row.  However I also need an addtional row for the final payment.

Is there a way to do this in DAX.  I can't use power query as this table doesn't show.

Any help greatly appreciated.

[Anonymous · Accepted solution]

Hi @PamWren 

For the requirement: Add addtional rows to this for each month between the start and end date, with the monthly payment populatated for that row.

You can refer to the following solution.

Sample data 

Create a new calculated table.

Table 2 =
VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            CALENDAR ( MIN ( 'Table'[Startdate] ), MAX ( 'Table'[EndDate] ) ),
            "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        ),
        [Month]
    )
VAR b =
    SUMMARIZE ( 'Table', [Startdate], 'Table'[EndDate], [Monthly Payment] )
RETURN
    FILTER (
        CROSSJOIN ( a, b ),
        EOMONTH ( [Month], 0 ) >= EOMONTH ( [Startdate], 0 )
            && EOMONTH ( [Month], 0 ) <= EOMONTH ( [EndDate], 0 )
    )

Output

For the second requirement: need an addtional row for the final payment

You can consider to use a matrix visual or table visual it can sum the totalpayment at final.

e.g 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.