Dax Waterfall Calculation Help

[Anonymous]

Hi Power BI Community! I am fairly new to Power BI and am hoping someone here would be kind enough to help me out. I am trying to create a waterfall visualization based on three particular columns in my dataset (ticket number, open date, and closed date), looking like below. There are additional columns in the set, but I am only interested in these three for my waterfall. I would like break down how many tickets were open (adding 1 per ticket opened) and how many were closed (subtracting 1 per ticket closed) each month. I would also like to make it possible to filter by the month and drillthrough my report from my waterfall. To do this, I believe I would need to create another table whether using Dax or M, counting the number of tickets opened and closed, and adding the results from the previous months to the next month if that makes sense. If this doesn't make sense, please let me know, and I can explain further/better. Does anyone know how I can solve my problem? Thank you! 

Ticket Number	Open Date	Close Date
1	1/3/2018	4/8/2018
2	1/23/2018	4/2/2018
3	6/3/2018	8/9/2018
4	6/30/2018	9/4/2018
5	9/18/2018	10/3/2018
6	10/10/2018	9/9/2019
7	12/30/2018	1/2/2019
8	3/22/2019	7/1/2020
9	4/9/2019	8/9/2020
10	4/28/2019	9/8/2020
11	7/8/2019	1/2/2020
12	8/9/2019	3/9/2020
13	9/10/2019	10/9/2020
14	11/4/2019	4/9/2020
15	2/2/2020	9/3/2020
16	3/4/2020	8/4/2020
17	5/8/2020
18	7/20/2020
19	9/7/2020
20	9/20/2020

[VijayP]

@Anonymous 

https://drive.google.com/file/d/1tl8wg3TiyEJTo7eP_NTRu9I8YD10qlkw/view?usp=sharing

Let me know if this works

[Anonymous]

---

@VijayP wrote:

@Anonymous 

https://drive.google.com/file/d/1tl8wg3TiyEJTo7eP_NTRu9I8YD10qlkw/view?usp=sharing

Let me know if this works

---

Thank you so much, @VijayP! This is certainly closer to what I'm looking for. Do you by any chance know a way for me to break down the waterfall to dates? I'd like to be able to show the dates on the axis (by month and year, but that's hopefully easy to do by drilling down the hierarchy)

[v-shex-msft]

Hi @Anonymous,

AFAIK, current power bi waterfall visual not support custom its breakdown, they will recognize and analysis based on your table structure. I'd like to suggest you to consider use custom visuals or try to use r visual to use script manually plot the graph.

Regards,

Xiaoxin Sheng

[VijayP]

@Anonymous 

You can use WaterFall chart from Xviz from appsource and you can get the same effect

[Anonymous]

@VijayP , I'm actually trying to find a way to show the difference between how many were opened to how many were closed per month. So if January 2018 had 2 open tickets and 1 closed ticket, altogether, I would get 1 open ticket that month if that makes sense. And if February 2018 had 4 more open tickets and no closed tickets, then adding to January, I would have 5 altogether by the end of February. Hoping the waterfall visual will automatically show the difference in count between the two months like it has done with my simpler visuals.

[VijayP]

@Anonymous 

https://drive.google.com/file/d/1tl8wg3TiyEJTo7eP_NTRu9I8YD10qlkw/view?usp=sharing

Check this

[amitchandak · Accepted solution]

@Anonymous , I have a blog on a similar type of Data. The waterfall is not there. but the required measures are discussed.

refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970