Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey all, I would like to creat a visual showing the history of e.g. number of tickets with different status per month.
Ticket | Accepted | Start Date | End Date |
1 | 31.03.2019 | 03.04.2019 | 30.06.2019 |
2 | 31.03.2019 | 03.04.2019 | 15.07.2019 |
3 | 30.04.2019 | 24.05.2019 | 16.06.2019 |
4 | 30.04.2019 | 12.06.2019 | 20.07.2019 |
Should show something like ...
Does anyone has an ideas how I can solve that? And there are not just 4 but many many tickets ...
Would be very happy to get an ideas ...
Best regards and thank you so much,
Hauke
Solved! Go to Solution.
Hi @Hauke ,
You need a separated Date Table, then you need to create the following two measures:
Measure =
CALCULATE (
COUNT ( t2[Ticket] ),
FILTER (
t2,
MONTH ( t2[Start Date] ) > MONTH ( MIN ( 'Table'[Date] ) )
&& MONTH ( t2[Accepted] ) <= MONTH ( MIN ( 'Table'[Date] ) )
)
)
Measure 2 = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, MONTH ( t2[Start Date] ) <= MONTH ( MIN ( 'Table'[Date] ) ) && MONTH ( t2[End Date] ) >= MONTH ( MIN ( 'Table'[Date] ) ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hauke ,
I am not sure if the following measures are the result of your desired, adjust the conditions inside the measure, you may get the results you want:
Before Start = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Start Date] ) * 100 + MONTH ( t2[Start Date] ) > MIN ( 'Table'[Year Month Number] ) ) && ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) && t2[End Date] <> BLANK () ) ) )
In Progress =
CALCULATE (
COUNT ( t2[Ticket] ),
FILTER (
t2,
YEAR ( t2[Start Date] ) * 100
+ MONTH ( t2[Start Date] )
<= ( MIN ( 'Table'[Year Month Number] ) )
&& (
YEAR ( t2[End Date] ) * 100
+ MONTH ( t2[End Date] )
>= ( MIN ( 'Table'[Year Month Number] ) )
)
)
)
No Start = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) ) && t2[Start Date] == BLANK () ) )
Not End = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Start Date] ) * 100 + MONTH ( t2[Start Date] ) > MIN ( 'Table'[Year Month Number] ) ) && ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) && t2[End Date] == BLANK () ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hauke ,
You need a separated Date Table, then you need to create the following two measures:
Measure =
CALCULATE (
COUNT ( t2[Ticket] ),
FILTER (
t2,
MONTH ( t2[Start Date] ) > MONTH ( MIN ( 'Table'[Date] ) )
&& MONTH ( t2[Accepted] ) <= MONTH ( MIN ( 'Table'[Date] ) )
)
)
Measure 2 = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, MONTH ( t2[Start Date] ) <= MONTH ( MIN ( 'Table'[Date] ) ) && MONTH ( t2[End Date] ) >= MONTH ( MIN ( 'Table'[Date] ) ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Joesh,
so I missed to brief two topics - I learned now with your great solution ... the one thing I mentioned before - we also need to consider the year. And the second topic ... obvisiouly it is possible, that a ticket is not started or closed, yet ... so we also need to count the tickets in measure 1 not heaving a starting date and in measure 2 not having and end date 😞
Could you give me a hint?
Hi @Hauke ,
I am not sure if the following measures are the result of your desired, adjust the conditions inside the measure, you may get the results you want:
Before Start = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Start Date] ) * 100 + MONTH ( t2[Start Date] ) > MIN ( 'Table'[Year Month Number] ) ) && ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) && t2[End Date] <> BLANK () ) ) )
In Progress =
CALCULATE (
COUNT ( t2[Ticket] ),
FILTER (
t2,
YEAR ( t2[Start Date] ) * 100
+ MONTH ( t2[Start Date] )
<= ( MIN ( 'Table'[Year Month Number] ) )
&& (
YEAR ( t2[End Date] ) * 100
+ MONTH ( t2[End Date] )
>= ( MIN ( 'Table'[Year Month Number] ) )
)
)
)
No Start = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) ) && t2[Start Date] == BLANK () ) )
Not End = CALCULATE ( COUNT ( t2[Ticket] ), FILTER ( t2, ( YEAR ( t2[Start Date] ) * 100 + MONTH ( t2[Start Date] ) > MIN ( 'Table'[Year Month Number] ) ) && ( YEAR ( t2[Accepted] ) * 100 + MONTH ( t2[Accepted] ) <= MIN ( 'Table'[Year Month Number] ) && t2[End Date] == BLANK () ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Joesh,
I guess I found the issue ... if I add another year - it seems to crash it ...
Leads to the following result 😞
Cab you help me with this again?
@v-joesh-msft
Porbably just without MONTH ...
Hey Joey,
this is perfect - exactly what I was looking for. Couldn't get it into my file so far... I will try and keep you updated. But definetly this is the solution! Thank you so much for your time and motivation!
Best regards,
Hauke
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |