Chart with multiple filters, slicers & 2 date columns

[dodonald]

Hi all, I am a complete newbe to PowerBI. As in, I started this morning. I managed to get all my data from 8 different tables in and started building visualizations. And now I'm stuck b/c I'm using two columns with dates.

I need to show a chart of the # of tasks assigned to and completed by a certain role over time. I also need to be able to filter by our various businesses & sites. I got stuck on the single relationship from a date table to a date column, so I created a duplicate table and linked to the 2nd date column.

The problem I have is that the SLICER only works for one of the tables (lines on the graph) and not the other. Is there a way to replicate this graph with only 1 table so the slicers works?

Expected result (And current result WITHOUT splicing):

Result with slicing applied (to only 1 table):

I believe I can use the userelationship term somehow (And no duplicate table), but I have no idea how.

File 

Thank you!!!

[jdbuchanan71]

Nice, glad you were able to figure it out!

[jdbuchanan71]

You would have to share all the measures that are going into the calculation.  Also, you should show the values in a table, show each of the measures that go into the calc.  It's easier to see what is going on rather than looking at a chart.

[dodonald]

Here is the the file Latest version 

The specific measures are:

[dodonald]

@jdbuchanan71  I figured it out! The Count by Timestamp was including rows with blanks, so created another measure to minus out the blanks and now the Running Open measure is working beautifully. Thanks for the help.

[jdbuchanan71]

@dodonald 

You could do a running total opened through the previous day and a running total closed through the prvious day then subtract rt closed from rt opened and add that onto the newly opened today.  Running total measures look something like this.

Running Total Created = 
VAR _MaxDate = MAX ( Dates[Date] )
RETURN
CALCULATE(
    [Count by Created],
    FILTER ( ALLSELECTED ( Dates[Date] ), Dates[Date] < _MaxDate ) )
)

[dodonald]

Hmmm... I am getting negative numbers...For example, when I last ran the data on Jun 27, I had 620 open tasks so I would expect to see 620 on 6/27.  Any ideas?

[jdbuchanan71 · Accepted solution]

@dodonald 

We can link the dates table to both the [WI Created/Assigned.1] and the [WI Vote Timestamp.1] columns then we can use USERELATIONSHIP in a CALCULATE to change which date column the Dates table links to.  No need for an extra date table or fact table.

Count by Created = COUNTROWS('DPY Task Data')

With the CALCULATE we can activate the inactive relationship (the one with the dotted line).

Count by Timestamp = 
CALCULATE(
    COUNTROWS('DPY Task Data'),
    USERELATIONSHIP('DPY Task Data'[WI Vote Timestamp.1],Dates[Date])
)

 I updated the relationships, removed the extra tables and added the measures to the updated model attached.

[dodonald]

@jdbuchanan71  wondering if you can help me with 1 more thing with this file...The chart you helped fix shows the number of tasks opened each day and closed each day. However, there's a "Backlog". For example, if I open 50 each today and tomorrow, but only close 45 each today and tomorrow, then I go into the 3rd day with 10 already open. How can I show that burn up/down on the same area chart? I tried just doing a measure to subtract the closed from the open, but then I only get the difference in opened and closed per DAY, not including any that were already open.

[dodonald]

Oh my word thank you sooo much! I completely missed the button for "Add new measure". I kept trying to add a column and couldnt figure out what I was calculating! Thank you thank you thank you!