SharePoint online list - LineChart with two date columns

[Anonymous]

Hi Community

I need some help with a report. I have two tables (1x SharePoint online list, 1x Date table).

In the SharePoint list I have two entries. One with a date (Created) and one with a date (Finished).

I want create a LineChart, where you can see how many entries were created per month and how many were closed.

I tried to create two measures, the Created and Finished date measure:

Created: 

Anzahl erstellt = COUNTA('Verwaltung Kaizenkarten'[Created])

Finished:

Anzahl Umgesetzt = CALCULATE(COUNTA('Verwaltung Kaizenkarten'[Abgeschlossen am]))

Here a overview with the LineChart:

Here the tables:

Here the Date Table:

But I only get the total, but not per month. Also, I don't know how exactly to connect the two tables in order to get the chart right.

Thanks for your inputs.

regards

Thomas

[TeigeGao]

Hi @Anonymous ,

In your scenario, we can use the following measure: 

CALCULATE(COUNT('Verwaltung Kaizenkarten'[Created]))

CALCULATE(COUNT('Verwaltung Kaizenkarten'[Abgeschlossen am]))

Best Regards,

Teige

[Anonymous]

I changed the measures, but it shows also only the total on every month. Should i create a relationship between the two tables? and when yes, what kind of?

best regards

[TeigeGao]

Hi @Anonymous ,

Yes, you need to create a relationship between these two tables, then the calendar table can filter the data table.

Best Regards,

Teige

[Anonymous]

ok, can you tell me what relationsship in this example would be the right one?

i've tried it with:

("Abgeschlossen am to date" and "created to date")

Result:

Result:

[TeigeGao]

Could you please share some sample data? 

[Anonymous]

Here some Items from the sharepoint list, for example: