Single Calendar table for 2 different dates from separate tables and date count.

[Redfox_delta1]

Hi Power Bi Community,

I am trying to do 2 things.

I need connect 2 dates which are in two separate tables into a single calendar table. I have read the articles on how to do this but I still can not get it working. I would also like to see how a date slicer would work with single date calendar table which has a Many to one relationship (single direction) with both tables. ideally I would like to see the calendar date slicer change data in a simple table.

 Sample file https://drive.google.com/file/d/1zrk5L5ls_RPcWQjNSXxp6C1PTgTA635U/view?usp=share_link

2) In the same file I am trying to create a function without success which creates the table below.

a) Uses the calendar date which pulls in the Lee and  Opus create date into calendar. (Column A)

b) Counts all the dates in (Column B)

c) Total the counts for each day in (Column C)

d) Finally seeing the calendar date slicer change the data in the table below.

Thank you for your help

[lbendlin · Accepted solution]

Your problem is that your SFDC leads timestamp comes over as DateTime, and you don't explicitly change that in Power Query.  This makes it impossible for the Calendar table to match your fact tables.

See attached for a corrected version.

[lbendlin]

yes, I used the implicit measure feature.

[lbendlin · Accepted solution]

Your problem is that your SFDC leads timestamp comes over as DateTime, and you don't explicitly change that in Power Query.  This makes it impossible for the Calendar table to match your fact tables.

See attached for a corrected version.

[Redfox_delta1]

@lbendlin I figured it out thank you. I see you went to date and count.

[Redfox_delta1]

Hi @lbendlin   thank you for your help. Can I double check what did you use to count that dates. I can not see any code which counts the dates so I must be missing something. Please can you kindly point me in the right direction.

[Redfox_delta1 · Accepted solution]

@lbendlin thank you very much for your help.  You solution works perfectly well.