How to filter distinct count across multiple dates

[frieso]

Hi everybody

I have a table with a List of opportunities, having dates when the first call of the opportunity took place, when the meeting took place, date of sending the offer, date of receiving the signed contract.

I now want to make a table, showing on an employee base how many calls, meetings etc an employee had in a given time of period.

i.E

Filter:

From Date 1.1.2017

To Date 18.1.2017

The table then shows:

Employee	Calls	Meetings	Offers	Contracts
Tom	10	5	2	3
Chris	3	3	3	1
Mike	10	6	2	10

As all the dates are independant and in multiple columns, how can I filter the table to sum up the values?

How can i let the user set the from/to dates on a visual basis?

Thanks for your help!

Frieso

[v-qiuyu-msft · Accepted solution]

Hi @frieso,

Assume the source table like below:

You can open Query Editor, select columns Calls, Meetings, Offers and Contracts, then click Unpivot Columns button get the format below:

Apply the modification, go to the report, create a matrix and slicer visuals.

Best Regards,
Qiuyun Yu

[MattAllington]

First I suggest you unpivot your columns so you have a single date column and a type column (e.g. Call, meeting, contract etc). You can then use a timeline slicer on a calendar table joined to your single date column to filter the period you want. I guess a countrows of the data table will do it.