Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a below table :
Ticket | Date Created | Date Closed | Month it is open | ||||||
January | February | March | April | May | June | ||||
1 | 1-Jan | 31-Jan | Jan, | Jan | |||||
2 | 1-Jan | 1-Feb | Jan,Feb | Jan | Feb | ||||
3 | 15-Mar | 1-Jun | Mar,Apr,May,Jun | Mar | Apr | May | Jun | ||
4 | 31-May | 1-Jun | May,Jun | May | Jun | ||||
5 | 8-Feb | 1-Apr | Feb,Mar,Apr | Feb | Mar | Apr | |||
6 | 1-May | 15-May | May | May | |||||
7 | 31-Jan | 1-Jun | Jan,Feb,Mar,Apr,May,Jun | Jan | Feb | Mar | Apr | May | Jun |
8 | 15-Jun | 18-Jun | Jun | Jun | |||||
9 | 1-Mar | 1-May | Mar,Apr,May | Mar | Apr | May | |||
10 | 1-Jan | 2-Jan | Jan | Jan |
and I need to calculate the the count like below:
Month | Count of In Progress |
Jan | 4 |
Feb | 3 |
March | 4 |
April | 4 |
May | 5 |
June | 4 |
so For each ticket add one to the count open/month from the month the ticket was opened through (and including) the month it was closed.
here is my below data source
https://drive.google.com/file/d/1cu-MhCMuPNJiW-p8Pn9a-AkWl-GBqYmL/view?usp=sharing
Hi @parry2k
,
I have used the below expression,
and I have created a Date table like below:
@sandip , refer this can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Just do distinct count of months
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |