Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
willpage
Helper II
Helper II

Data Transofrmation Challenge

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:

willpage_2-1645646687165.png

 

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:

willpage_1-1645646516580.png

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.

 

1 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.

 

 

View solution in original post

3 REPLIES 3
v-eqin-msft
Community Support
Community Support

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
Super User
Super User

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

 

@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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.