Batvatar
New Member
- Joined
- Jul 23, 2015
- Messages
- 33
Hello everyone, i'm pretty stumped here.
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.. (so i'm guessing count of "Left Date" * -1?) 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!!
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.. (so i'm guessing count of "Left Date" * -1?) 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 |
