Expanding Table with Start and End Date with Monthly Payment

[jl20]

Hi,

I have a dataset with customer, monthly payment amount, next payment date, and final pmt date. Any way in M or DAX to "expand" the table such that each monthly pmt will become its own row? For example, I have a customer that owes 20 more payments of $1000 and that currently sits in one row with start date = end of this month, end date = EOM + 20. How do I expand that so that said customer has 20 rows (with each monthly pmt date in its own row), plus all other customers' row are expanded as well? For example, if I have 100 customers with 20 motns each left, resulting table would have 2,000 rows.

Ultimately looking to model cash flow based on monthly totals from this table. I will relate to calendar table w/end of month values and calc IRR, etc.

Thanks in advance!

[arch_1]

Hi.

There is one simple method to solve the problem.

In Power Query Editor click Add Column tab - Custom Column (Start_Date and End_Date must be 'data' type).

Click Add Column tab > Custom Column and add the  =expression {Number.From([Start_date])..Number.From([End_date])}

Two dots will generate a list from Start_date and End_date.

Extend created column Date with “Expand to New Rows”. Change extracted values to Data type.

The result table will be look like this.

Hope it will help you and others with the same problem.

[v-diye-msft]

Hi @jl20 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[DataZoe]

Hi @jl20 ,

You can do this in DAX by creating the date table and measure, then a expanded table:

Calculated Table:

Date =
ADDCOLUMNS (
    CALENDAR (
        MINX ( Payments, Payments[Start Date] ),
        MAXX ( Payments, Payments[End Date] )
    ),
    "Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)

Calculated Table:

Months = values('Date'[Month])

Measure:

Active Customers =
CALCULATE (
    COUNT ( Payments[Customer] ),
    FILTER (
        Payments,
        MIN ( Months[Month] ) >= MIN ( Payments[Start Date] )
            && MIN ( Months[Month] ) <= MAX ( Payments[End Date] )
    )
)

Calculated Table:

Expand =
SUMMARIZECOLUMNS (
    Months[Month],
    Payments[Customer],
    Payments[Monthly Payment],
    Payments[Start Date],
    Payments[End Date],
    "Active", [Active Customers]
)

The key is not to make a relationship between the tables. 

[amitchandak]

@jl20 , done something similar in this file. See if this can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[jl20]

This didn't seem to be what I was looking for.

[DataZoe]

@jl20 Did you see the new financial functions that came out in the latest release? I was wondering if they may solve your problem without needing to expand the table.  More information on those are at these links:

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-2020-feature-summary/ 

https://docs.microsoft.com/en-us/dax/financial-functions-dax 

Also, if you can elaborate on why the solutions proposed were not solving your question, I'd be happy to take another look. 

[parry2k]

@jl20 try this code, start blank query and paste code in advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR04x8hAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start])..Number.From([End] )}),
    #"Expanded DateRange" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded DateRange",{{"Date", type date}})
in
    #"Changed Type1"

Logic is in added custom column step, and then expand it and change the type to date. Start and End in custom column will be your date column.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡