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.
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))
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.
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?
Solved! Go to Solution.
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.
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.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.