best table setup to begin a Ticket based data model

[Anonymous]

Hello everyone,

This is my first data model & I would like to have some advice on best table setups before doing any calculation.

I have a classic, fact table with one row per ticket with a status + extra details & also  4 different dates dimensions I need to use to calculate basic aging but also some advanced aging flagging based on different criteria contained in the model.

I have a calendar table with all the details i need & that i can replicate if need be.

The aging will need to be calculated between creation date & close date (some tickets won't have a close date).

I want to be able to use any relative date to have a view of my open pipeline of tickets to date, my average versus last year, last Q, etc ...

Now, what I need the help of the community for is to understand if i need to break down the fact table calculate the status & aging of my cases the best & clean way possible or not .... & what kind of relationship i need to create between my calendar(s) & my table(s) for a basic calculation.

Thanks in advance for any feedback.

[Greg_Deckler · Accepted solution]

@Anonymous - Well, you will likely at some point need this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[amitchandak · Accepted solution]

@Anonymous , refer if this can help

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

[Anonymous]

Good afternoon, Thanks Amitchandak to tka of your tie to help me ., it helps a lot to see that a model of a fact table with 2 dates need for calculations. Super helpful

[Greg_Deckler · Accepted solution]

@Anonymous - Well, you will likely at some point need this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Anonymous]

Hello Greg, thanks for your input & this is a post I flagged in my personal favorites because I will surely use it soon.