cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Connecting Collection / Invoice Amount Collection / Invoice Date

Hi all,

 

I am working with a table in which each row contains data regarding a project that is being conducted. Each project has between 1 and 4 Invoices Amount in USD (where customers pay in bulks) as well as the same amount for Collection Amount in USD.

 

For each Invoice/Collection Amount there is a date so we know when the money were invoiced as well as received. This can be seen in the columns below:

 

CollectionAndInvoice1.JPG

 

 

CollectionAndInvoice2.JPG

 

 

I already have a Calendar Table.

 

My question is this:

 

How do I connect each of the Invoice / Collection Amount Columns to the respective Invoice / CollectionDate columns?

 

Do I need to duplicate the table 8 times and connect the specific Date fields to my Calendar Table, or how do I go about this?

 

Hope to hear from some PowerBI experts!

7 REPLIES 7
Greg_Deckler
Super User
Super User

I'm not understanding what you are going for here. Seems like there should be some kind of invoice id column and/or project id in your tables but I do not see it. Is each row a project? Possibly need to unpivot some columns. Difficult to tell. 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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Anonymous
Not applicable

@Greg_Deckler,

 

I apologies for the confusion. I think not being used to powerbi makes my way of explaining the problem confusing.

 

This is ideally what I would like to achieve, let us take two examples of projects done:

 

Project 1 50.000:

 

Invoice 1: 50.000

Invoice Date 1: 1st of January 2020

 

Collected (Received)1: 50.000

5th of February 2020

 

Project 2 100.000:

 

Invoice 1: 40.000

Invoice Date 1: 1st of January 2020

 

Invoice 2: 40.000

Invoice Date 2: 1st of February 2020

 

Invoice 3: 40.000

Invoice Date 3: 1st of March 2020

 

 

Collected 1: 40.000

Collected Date 1: 15th of January 2020

 

Collected 2: 40.000

Collected Date 2: 18th of February 2020

 

Collected 3: 40.000

Collected Date 3: 5th of March 2020

 

 

 

 

So in the above examples, I am trying to create a "Clustered Column Chart" in which the Invoiced/Collected Amount shows by Month.

 

So in that example I would have a graph that shows the following:

 

 

January 2020:

 

Invoiced Amount: 90.000

Collected Amount: 40.000

 

February 2020:

 

Invoiced Amount: 40.000

Collected Amount: 90.000

 

March 2020:

 

Invoiced Amount: 40.000

Collected Amount: 40.000

 

 

 

Does it make sense or should I provide any other information?

I *think* what you want is something along the lines of my Open Tickets quick measure. See here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

And I am definitely wondering now if you should not just unpivot your Invoice and Collected columns and this would probably make things much easier.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Anonymous
Not applicable

@Greg_Deckler,

 

Thank you for the link, I will have a look.

 

I know there is an "Unpivot" feature in the edit query, but is there any chance you ellaborate on what you mean and how you would approach this issue with the healp of unpivot?

Sure, what I am thinking is that if you unpivoted your Invoice and Collection columns then you could use the column identifier that is now a row value in a single column to calculate your Date. Then you would have a single Date column for all of your Invoices/Collections that you could use as the Axis for your chart.

 

It is very difficult to say for certain because I am not certain how much is source data versus calculations in the data you provided.

 

If it is all data, then you could still unpivot your columns and use a conditional calculated column in Power Query to grab the correct date column value for your Date column. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Anonymous
Not applicable

@Greg_Deckler 

 

I think you are right. I just duplicated my maintable and unpivoted the 8 columns (4 Invoiced, 4 Collected) and it seems to be logically showing the data. Now you mention that I should:

 

"conditional calculated column in Power Query to grab the correct date column"

 

Can you guide me in a direction of what formula or what tool I need here? After unpivoting, my table looks like this:

 

 

CollectionAndInvoice3.JPG

 

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!