How to connect values to specific date columns

[Anonymous]

Hi all,

I have the following table - all data is raw data, no calculated fields as of right now:

I have unpivoted the table according the 8 different columns:

4 Invoice Amount (currency)

4 Collected Amount (currency)

There are correspondingly:

4 Invoice Date columns

4 Collected Date columns

The reason for this is that each project has up to 4 different invoices/collection amounts with dates for each of these.

Now I wish to be able to make it so that:

-"Collected 1" (currency) connects to the "Collected Date 1",

-"Invoice 1" (currency) connects to the "Invoice Date 1",

"Collected 2" (currency) connects to the "Collected Date 2",

-//-

-//-

......

In other words I would like to track what is being invoiced in which months and likewise what is collected for each month.

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

[v-jayw-msft]

Hi @Anonymous ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EricHulshof]

Hey,

I believe if you format the data like:

Attribute                  Date                        Value
Collected 1              01-01-2020             5000

Invoiced 1                01-01-2020             5000
etc

You can get the desired results 🙂

You might even split up attribute in "Collected" and "1" to make it easier to select all collected or just the first collection.

[Anonymous]

@EricHulshof 

Okay I think I am getting closer to a working solution. I have done the following:

Made two duplicates of the original table and called one "Collected-Table" and the other "Invoiced-Table".

Now I have deleted all the Invoiced Date and Amount from the "Collected-Table" and deleted all the Collected from the "Invoiced-Table".

Now I have Pivoted the Dates columns as well as the collected/Invoiced Amount in each of the tables.

However, now I see one issue:

IF a project has more than one collection, then the "Value" gets duplicated. I guess I can use the "Remove duplicates" from the "Value column, but what happens then if at one point a collection matches a collection from a different project? Then the data would not show?

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You can use DISTINCT() function to create a calculated table.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EricHulshof]

Hmm, the collected values of 1 project will allways be the same?  Like:
Project 1 > Collected 1 (date) 5000 > Collected 2 (date) 5000? 

If do. It is not a big problem to leave it. Else you have indeed a chance to delete data you do not want to delete. You can easily get the MAX,MIN,DISTINCT or whatever value of that project, because it will allways be the same. 

[Anonymous]

@EricHulshof,

Not sure I understand, how do you do that?