Stacked Bar or Line Chart Using Multiple Data from Same Table along with a Date/Calendar Table

[gemcityzach]

Hello,

I'm trying to plot various types of time intelligence metrics on the same chart. I have a single table, that is a SharePoint list, that has four date fields. I also have a Calendar/Date table and I have created relationships to these four date fields. Created Date is primary/active and the rest are inactive.

I'd like to be able to plot Created (count of created) and Closed (count of closed) in a stackbar chart or even a line chart of counts of labels by Created and Closed. I know I need to use the USERELATIONSHIP function so I can activate Created or Closed in a Measure to count/plot relative to the Calendar/Date Table. But I'm not sure the best way to do it.

ID	Label	Identified	Created	Decision	Closed
1	Shoes	3/1/2024	3/1/2024	3/31/2024	3/31/2024
2	Guitars	3/2/2024	3/2/2024	3/31/2024	3/31/2024
3	Shoes	4/2/2024	4/2/2024	4/5/2024	4/5/2024
4	Balloons	4/22/2024	4/22/2024	4/23/2024	4/23/2024
5	Cheese	4/5/2024	4/22/2024	4/23/2024	4/23/2024

[Alex87 · Accepted solution]

Count Closed =

CALCULATE(

    COUNT(Data[Closed]),

    USERELATIONSHIP(Dates[Date], Data[Closed]))

Count Created =

CALCULATE(

    COUNT(Data[Created]),

    USERELATIONSHIP(Dates[Date], Data[Created]))

As you have an active relationship on Date created you can use directly as a DAX Measure = 

COUNT(Data[Created]) , but I like to keep the consistency between the formula patterns. It will work either way.

I slightly changed your sample data as the count was the same between the two

If it answers your query please mark my post as a solution

[gemcityzach]

Is there a way to ensure that it's only pulling back fields with an actual closed date populated? My source is a SharePoint list and empty closed dates are populated with 'null' in PowerQuery. I tried adding a filter [Closed_Date] <> "" and also [Closed_Date] <> null, and other variations but it fails.

[Alex87]

It is a weird behavior. You can try different ways to filter out data

if this does not work:

Table.SelectRows(#"Removed Columns", each [Closed] <> null and [Closed] <> "")

try filter data after a certain date:

Table.SelectRows(#"Filtered Rows", each [Closed] > #date(2020, 1, 1))

or maybe you can duplicate the column and transform the datatype to whole number and filter out the nulls or values above a certain amount.