Forum Discussion
Multiple Dates
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
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/
4 Replies
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/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 🙂
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!
