Calculate OPEN CASES over time in Power BI | Part 2

[@HowtoPowerBI]

if you try it let me know!

[@xodrinker]

Perfect, have an actual case I need this in - thanks!

[@rachidachaib-j8n]

Thank you sooo much the way you explain is Amazing

[@ballofwax2586]

great!!! very helpful... thanks so much. You do a great job at explaining how everything works and why.... easily one of my new favorite channels to learn with.. I am using these two videos you made to show active subscriptions.. My next feat is to figure out the dates on which these users did not renew their subscription... or in the case of active employees.... the dates at which employees left, to see if there is a pattern.

[@charlieschmidt6478]

This worked great but I am having trouble with getting slicers to interact with the visual correctly. Slicer/filter selected option does not affect the open case/active employee count. Any tips would be appreciated!

[@yadukrishnankv5265]

Hi Bas, awesome video! I've got a question though. If I'm trying to forecast the number of employees for the next few years (I have their planned leaving date) what edits should I do to the DAX? With the CurrentDate as in the video, it is calculating only till today(as I have my last day in my calendar table set to today). But when I changed last day to dec 31 2040, the DAX is not working as expected.

[@arvgdv]

this was very helpful!

[@HowtoPowerBI]

Happy to hear this!

[@jacodj5413]

Hi Bas, Can this technique be adapted in some way to allow viewing the records (I.e. employee names) which make up the count? Thanks!

[@agelikima4069]

Simply explained and great job. Uppon this example would there be any way to calculate the percentile of days of open cases over time? thank you in advance

[@HowtoPowerBI]

Pattern would be the same. You would need to exchange the employee count measure with the measure you want

[@agelikima4069]

@@HowtoPowerBI thank you very much :)

[@MrScotee]

Great explanation! What if i wanted to sum or avergae the total tenure of employees at each point in time? Is this possible?

[@josefinagaccio2362]

Hi! If I need for example to calculate the same but only for working days, how can I estimate that? For example open tickets for more than 30 working days

[@angelgutierrez2781]

Awesome work!!!!! Would you be able to to average? Like in May there where 10 active and 5 where above 1 year. Average for may over 1 year is 50%

[@lijunchen]

Hello Bas, This is a great tutorial. I have racked my brains to figure out how this can be done with no success. This is the only solution I have found on youtube or elsewhere to the case for historical count of open cases by dynamically calculated tenure or age groups. But I do have a question or suggestion. I wonder instead of creating a measure for every tenure group, is it possible to create a measure for the tenure or age group and use it as a legend to create the chart of the number of active open cases across time by the dynamic age or tenure group. This will help a lot if there are more than one KPIs (active employees, their wages, sales, workload, etc) that will be displayed in the same chart using the field parameters.

[@conradoiiaustria5277]

Hey men, could you do a video if there is a rehire date on the same row of data

[@HowtoPowerBI]

wouldn't that be kinda the same thing,.. just extend the logic for the filter conditions?

[@daveschorah9174]

Fantastic; adding time intelligence to my employee tracker has been the last thing that I had to accomplish and I've been tearing my hair out trying to work it out - thank you! The only thing I can't do is have the chart laid out by Qtr then year like in yours - I can only get the years 2014-2021 in the chart despite having date hierarchy in the axis field...?

[@Westrackted]

A bit late, but you have to turn off concatenate labels and drill all the way down. I assume that that's what you meant

[@jaggyjut]

I have helpdesk data and trying to build a visual to show the open and close tickets in a given period like day, month or quarter. Im not sure how to count a ticket that was open and closed in the same day?

[@celsodemetriojusto]

Did you ever find out how to do this?

[@jaggyjut]

@@celsodemetriojusto not yet in powerbi.

[@adrianlock8484]

@@celsodemetriojusto basically just count Tickets which have same created & resolved Date. So you could use something like this for anyone else after similar _TurnedARoundinPeriod = CALCULATE ( countrows(FACT_Faults), DATESBETWEEN ( FACT_Faults[CreatedDate], MIN ( DIM_DateCalendar[Date] ), MAX ( DIM_DateCalendar[Date] ) ), FORMAT ( FACT_Faults[ResolvedDate], "mm,yy" ) = FORMAT ( FACT_Faults[CreatedDate], "mm,yy" ) )

[@lalitkaushik1812]

Pls share video on open tickets and close tickets on weekly basis in IT dept.