Forum Discussion
Use Calendar for Time Intelligence on another Table with Multiple Dates
What do you think the optimal way to plot muliple dates from the same table would be in a combo column-line chart?
I have work units that have a 'Created' date and a 'Closed' date. I want to implement good PowerBI practices so I created a Calendar table and I've linked Created date to the 'Date' field in the Calendar column. However, I cannot have more than one active relationship.
On my column chart I want to plot the number of items created and then the secondary line plot should show the number of items closed in that same month. And I want the x-axis to show Month-Year as opposed to Month-Day like it is now.
Is there a way to do this and to use the calendar table for both Date fields (created, closed)?
gemcityzach Yeah, doesn't work that way other than perhaps overlaying two visuals but that's a mess. Better to create a measure for the closed count of ids like this:
Closed Count Measure = VAR __Date = MAX('Date'[Date]) VAR __Table = FILTER( ALL('Table'), [Closed Date] = __Date ) VAR __Result = COUNTROWS( __Table ) RETURN __Result
7 Replies
gemcityzach
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365If not, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.hey greg thanks for responding! those two options look nice but not quite what im looking for. sample data:
ID Group Event_Type Created Closed DueDate PastDue 1 Group1 A;C 03/01/2024 null 03/15/2024 Yes 2 Group2 B 03/15/2024 null 03/30/2024 Yes 3 Group3 A 04/12/2024 04/18/2024 04/27/2024 Yes 4 Group1 B;A 04/01/2024 04/18/2024 04/16/2024 No 5 Group1 A;B;C 04/15/2024 04/18/2024 04/27/2024 No Column Chart X: Month-Year Created
Column ChartY: Count of IDs
Line X: Month-Year Closed
Line Y: Count of IDs
gemcityzach Yeah, doesn't work that way other than perhaps overlaying two visuals but that's a mess. Better to create a measure for the closed count of ids like this:
Closed Count Measure = VAR __Date = MAX('Date'[Date]) VAR __Table = FILTER( ALL('Table'), [Closed Date] = __Date ) VAR __Result = COUNTROWS( __Table ) RETURN __Result
