Multiple Dates

[DonBisset]

Hi

I'm really stuck and hoping you can help.

My module contains 

• A fact table for TASKS, containing multiple date fields, CREATED DATE, DUE DATE, COMPLETED DATE
• A date table with an active relationship to CREATED DATE

My challenge is to create a report visual with the calendar on the x-axis, for example: 

• Feb 2021, [Sum of Tasks Created], [Sum of Tasks Due], [Sum of Tasks Completed]
• March 2021, [Sum of Tasks Created], [Sum of Tasks Due], [Sum of Tasks Completed]
• and so on

My problem is that the output shows the sum of records that match the active relationship, CREATED DATE.  In other words, a task completed in March would count in the month the task was created.

This must be a common issue but I must be googling the term.

Thanks in advance

[joshyT7]

As @aj1973 stated above you can use the USERELATIONSHIP function to help you here. 

Create two inactive relationships for your other date columns and then you would need to specify them within a CALCULATE function. 

For example: 

CALCULATE ( SUM ( tasks ), USERELATIONSHIP ( tasks[due date], date[date] ) )

This will use the inactive relationship between due date and your date table rather than the relationship with created date. 

Hope this helps! 

[aj1973]

Hi @DonBisset 

To your Model add 2 other inactive relationship between Due date and Completed date to the Calaendar table.

Then add 2 measures using Dax formula USERELATIONSHIP to get the sum for due and completed tasks

[jdbuchanan71 · Accepted solution]

Hello @DonBisset 

It is a pretty common problem.  Take a look at this post that discusses how to handle it.
https://blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/

[DonBisset]

Thanks..  your detailed post was exactly what I needed to work through this.   Logically it makes sense now, whereas before creating a measure seemed counter-intuitive.  This has opened lots of doors to measures now, my model is going to get busy 🙂