Waterfall Chart based on counts of 2 date fields on the same row item..

[Mastuto]

Good Morning everyone, 

I've been trying to figure this one out for a few days and have attempted to research online for some solutions, but i'm having a hard time finding use cases for this particular situation.. I'm currently trying to create a waterfall chart in Power BI but based on the count of 2 separate date fields that exist on the same row record. I've included a table with an example of the data set, and an image of what i did to get the desired results in Excel; 

in the example, we have accounts that are created that recieve a "Created Date" and a unique "Account ID".  additionally, when an account exits the program, that same record recieves an "Exit Date". I'd like to see the count of accounts created Month over month for the last 2 years (2020 - 2021 YTD), as well as a subtraction month over month when they exit.  On tab 2 highlighted in yellow is the ideal view, where each month represented has a Postitive count for accounts created, and a negative count for accounts left on the appropriate space for their creation and exit month. (I know this duplicates the month, so if a creation and exit happened in jan 2020, we'd see Jan 2020 bar twice, one with a positive and one with a negative view, in our program there will almost always be gains and losses each month)..

I believe all we should need is the Account unique ID (could possibly count those vs counting the dates); the created date and exit date.

Not every record in the data set will have a created date, but not every record will have an exit date. A created account can have an exit date at anytime after the created date. the created and exit dates exist on the same record line item (same row); 

if anyone can assist here that would be fantastic, please see attached, thanks!! 

Account ID	Created Date	Left Date
55267	1/1/2020	5/1/2020
53031	1/2/2020	4/30/2020
84087	2/2/2020	3/1/2021
77832	2/6/2020	5/1/2020
21067	3/1/2020	5/7/2020
99100	4/6/2020	5/1/2020
23826	5/9/2020
32926	5/25/2020
84742	6/7/2020	1/1/2021
20309	6/12/2020
41700	7/15/2020
58192	7/20/2020
46004	8/1/2020
42509	9/10/2020	5/1/2021
98575	10/18/2020
37213	10/30/2020
6069	11/2/2020	3/1/2021
85655	12/21/2020	3/1/2021
86155	1/8/2021
5380	1/18/2021	4/1/2021
10320	2/18/2021
55794	2/27/2021	5/1/2021
40408	3/21/2021	5/1/2021
21313	3/28/2021
97774	4/14/2021
77542	4/27/2021
30569	5/7/2021
16636	5/24/2021

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mastuto]

Thank you, let me review this and i'll get back!

[lbendlin]

You can use one Dates table with active relationship on the create date and inactive relationship for the exit date, or you can use two independent dates tables. What's your preference?

[Mastuto]

active relationship on the create date and inactive relationship for the exit date i think makes sense, then you can virtually make the relationship on the inactive one active with a DAX formula?

[lbendlin]

yes, here's the function: USERELATIONSHIP function (DAX) - DAX | Microsoft Docs