Merge 2 fact tables and create measures on employee goals and performance

[Saule]

Hi all,

UPDATE1:

1. Added Example pbix with my both attempts (current table merge and old trough relationships)

2. Added expected result visualization (below My Goal)

I have 2 fact tables:

Table1 has employee goal data (GoalVisits), where goal target is per month, but GoalMonth format is (yyyy-mm-dd), with day allways 1. Table2 has data about employee visits to costumers, where VisitID is unique and created by date.

Table1:

User	GoalMonth	GoalVisits
A	2020-01-01	5
A	2020-02-01	6
B	2020-01-01	4
B	2020-02-01	4
B	2020-03-01	3
C	2020-03-01	8

Table2

User	VisitDate	VisitID
A	2020-01-14	2
A	2020-01-25	4
A	2020-02-18	3
B	2020-01-14	1
B	2020-02-25	5

My Goal:

1. be able to count each employee visits by various data intervals (per month or year or date interval) in visualization, so i could:

2. count each employee goal archievement (total visits per month/GoalVisits).

First I added new VisitDateMonth column to Table2:

= Table.AddColumn(Source, "VisitDateMonth", each #date(Date.Year([VisitDate]), Date.Month([VisitDate]),1))

Table2 (modified)

User	VisitDate	VisitID	VisitDateMonth
A	2020-01-14	2	2020-01-01
A	2020-01-25	4	2020-01-01
A	2020-02-18	3	2020-02-01
B	2020-01-14	1	2020-01-01
B	2020-02-25	5	2020-02-01

Then I merged both tables by User, GoalMonth and User, VisitDateMonth. Each user has few rows with same GoalMonth date, but different VisitDate as expected.

Now when I try to filter in visualization by month - VisitID count works fine. But if filtered in matrix by year and larger time period it automatically aggregates at year level.

Questions:

1. Did I merged the right way depending on My Goal (above)?

2. How to write measure to count employee visits (so it works on any date range)?

3. How to write measure to check GoalVisits target? Simple measure employee visits/GoalVisits?

[Ashish_Mathur]

Hi,

Ideally you should have a Calendar Table with relationship (Many to One and Single) from the VisitDate column and GoalMonth columns to the Date column of the Calendar Table.  Likewise, create a stanalone User table (with only unique entries) and build a relationship from the 2 tables to the this standaone table.  To any visual, drag Date from the Calendar Table and User from the standalone table.

Hope this helps.

[Saule]

Tried it before (old table in my example pbix). My problem still remains, how to count goals per year per user and Goal%.

[ryan_mayu]

@Saule 

what's the expected output based on your sample data?

[Saule]

I updated my first post with expected goal picture and example pbix

[ryan_mayu · Accepted solution]

@Saule 

is this what you want? pls see the attachment below

[Saule]

Thanks, thats the idea. I just added extra month column.

I see that you put count on VisitID and sum on GoalVisits in Model. Not sure why - I need to read more tutorials.

[ryan_mayu]

you are welcome