Query / Add Column Formula to expand cost per unit and number of units by duration

[Sulley]

Hi guys

Losing my head here ... and probably dead simple to create a formula for an added column.

Here's what I need to do ... create a profile of benefits over months - unpivoted in PowerBI so I can connect to my time intelligence calendar table:-

Source Data

 1) Monthly Benefit = $100 per month

 2) Benefit Duration = 36 months

 3) Benefit Start = Date

In Power Query Editor ... I need to traverse by the number of durations ... and create a row per month ... starting at the start date .  so I have granular benefits?

Does that make sense ... any help appreciated from this awesome community.

[V-pazhen-msft]

@Sulley 
Please follow the blog to expand the date, just change the duration from time to month.
Convert the start time and end time into multiple ... - Microsoft Power BI Community

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

Hi @Sulley ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDSNzIwMlTSUbIAYkMDA6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [benStart = _t, benDuration = _t, benMonthValue = _t]),
    chgSourceTypes = Table.TransformColumnTypes(Source,{{"benStart", type date}, {"benDuration", Int64.Type}, {"benMonthValue", Int64.Type}}),
    addBenLastDate = Table.AddColumn(chgSourceTypes, "benLastDate", each Date.AddMonths([benStart], [benDuration] - 1), type date),
    addBenDateList = Table.AddColumn(addBenLastDate, "benDateList", each List.Transform({Number.From([benStart])..Number.From([benLastDate])}, each Date.From(_))),
    expandBenDateList = Table.ExpandListColumn(addBenDateList, "benDateList"),
    filterDayMatch = Table.SelectRows(expandBenDateList, each (Date.Day([benStart]) = Date.Day([benDateList])))
in
    filterDayMatch

Summary:

1) Get the date of the last benefit payment (start + duration - 1)

2) Create list of dates between start and end dates

3) Filter rows where day element of date matches between start date and date list.

I've left it in steps so you can follow the process, but could easily be combined into one or two steps if you wanted to.

Pete

[Greg_Deckler]

@Sulley Seems like you would need to use something like List.Generate to do what you want. In DAX you would use GENERATESERIES. Not entirely sure of your source data or expected output though. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.