Data Transofrmation Challenge

[willpage]

I have a problem I need to solve which I hope someone clever can help me with.

I have a table of deals in a CRM system. Each deal has a first invoice date, a duration in months and a value.

I need to divide the value by the months (easy) and create a row for each month so I can plot the forecast work value over time.

For eaxmple I have this:

last_invoice_date and value_per_month are not from the source, I created those.

To plot on the chart I need the data to look like this:

Each deal could have a different number of months so I'd need a different number of rows for each one.

I don't care whether this is done in DAX or PQ but I guess for performance reasons, PQ could be better.

Any help greatly apprecaited.

[v-eqin-msft]

Hi @willpage ,

It seems that your issue has been solved ! 😀

If so, please Accept your reply as the solution to make the thread closed.More people will benefit from it. 

Best Regards,
Eyelyn Qin

[HotChilli]

You can create a list which adds 28 days (shortest days of any month) for each month in duration_months like this:

Add column

List.Dates([invoice_date], [duration_months], #duration(28, 0, 0, 0))

expand the list. (to rows)

Add a column (from the interface , select the list column, Add Column->Date section ->Month->End of Month)

Obviously it won't work correctly if the invoice lasts for more than 28 months.

I used 'End of Month' for the final column because I didn't think desired output shown was realistic, taking the date from the invoice date ,invalid dates could be produced e.g 31 January, 2 months might produce 31 February

---

Let me know how it goes

[willpage · Accepted solution]

@HotChilli  That's good, thanks. It was the kind of thing I was looking for, but I implemented something slightly different using your idea an inspiration.

I did

{0..[duration_months] - 1}

to create a list column called Number List

Then a new custom column with

Date.AddMonths([first_invoice_date], [Number List])

This way I'm not limited to the number of months the invoices can be spread across.

As an aside, in the meantime I did this in DAX using a date table:

Value by Date = CALCULATE( 
                    SUM ( deals[value_per_month] ),
                    FILTER( 
                        deals,
                        deals[first_invoice_date] <= MAX ('Date Table'[Date]) && deals[last_invoice_date] >= MAX('Date Table'[Date])
                        )
                    )

This actually works OK, except that if I put a line chart of value_by_month over months from my date table, and also a table visual with customer name and deal description etc on the report and no data point is selected on the chart, then the table only shows the deals which correspond to the last date in the date table. If I click on a data point it shows the deals that span that date, which is what I want, but with nothing selected it doesn't show all deals, it just shows the one that's furthest in the future.

Therefore this PQ method is better.