Connecting Collection / Invoice Amount Collection / Invoice Date

[Anonymous]

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:

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!

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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

[Greg_Deckler]

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.

[Anonymous]

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

[Greg_Deckler]

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. 

[Anonymous]

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