Use Calendar for Time Intelligence on another Table with Multiple Dates

[gemcityzach]

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)?

[Greg_Deckler]

@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/409365

If 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.

[gemcityzach]

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

[Greg_Deckler · Accepted solution]

@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

[gemcityzach]

Hey Greg that worked pretty well for me to convert it to a stacked bar chart. But now none of my on-page slicers work. I'm guessing I need to figure out a way to keep the 'Group' filter?

Edit: I changed FILTER ( ALL ) to FILTER ( KEEPFILTERS ...) and that worked! 🙂 Do you know how I might get the X-Axis to show Month-Year instead of MMM-DD?

[Greg_Deckler]

@gemcityzach Also, you could have used ALLSELECTED instead of ALL

[Greg_Deckler]

@gemcityzach That's typically done in your formatting for the column.

[gemcityzach]

Yeah I checked for that but it doesn't offer the ability to 'group' the data into months. There are just variations on MM-DD-YYYY. I used the SQIBI calendar template (DAX Date Template - SQLBI).