Forum Discussion
Handling Multiple dates inside visual
So I have a table with multiple dates: 'Opened Date', 'Closed Date', 'Maintenance Start Date', 'Maintenance End Date'.
Other than dates I have also 'Total Approved'.
I have created a calendar table and created inactive relationships to be able to handle all dates using USERELATIONSHIP.
Currently I have 2 Measures:
Total Approved by Maintenance End Date =
CALCULATE(
SUM ( Table[TOTAL APPROVED] ),
USERELATIONSHIP(
dCalendar[Date],
Table[MAINTENANCE END DATE]
)
)Total Approved by Closed Date =
CALCULATE(
SUM ( Table[TOTAL APPROVED] ),
USERELATIONSHIP(
dCalendar[Date],
Table[CLOSED DATE]
)
)
I can see each one separately in a column chart, but I wanted to have a consolidated view. I wanted to create a column chart which has Closed Date as X-axis, Total Approved as Values, and Maintenance End Date as Legend. Something like I did in this Excel below (but if I could group by Month, instead of Year, it'd be even better).
- Anonymous5 years ago
Anonymous
To do what you want you have to have role-playing date/time dimensions. You can't achieve it with just one Date table. Sorry. Please create as many date tables as there are dates you want to visualize independently.
7 Replies
- AnonymousNot applicable
Anonymous
To do what you want you have to have role-playing date/time dimensions. You can't achieve it with just one Date table. Sorry. Please create as many date tables as there are dates you want to visualize independently.
- AnonymousNot applicable
Makes sense...so I should create another date table (as I only want a visual with 2 of the dates), but then what? Should I create inactive relations between all dates again? Should I relate the date tables as well?
I was actually thinking about something more on the line of using Summarize to group the costs by maintenance end date, but I don't know if that would work.
- AnonymousNot applicable
Inactive relationships are created when one and the same table joins to another on 2 or more different fields. You don't have such a situation since in this case each date table will join to the same table on one (and different) field.
- Ashish_MathurSuper User
Hi,
Share the download link of your PBI file.
- AnonymousNot applicable
Sorry, I cannot share it as there are many sensitive data.