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.
Hi,
I need to calculate count tickets carrying over the month and below is the data set link:
https://drive.google.com/file/d/1zOPAPGx1ovtF4IdM80Li2WqCkjDYku7a/view?usp=sharing
and below is the data set look like:
Ticket | Date Created | Date Closed |
1 | 1-Jan | 31-Jan |
2 | 1-Jan | 1-Feb |
3 | 15-Mar | 1-Jun |
4 | 31-May | 1-Jun |
5 | 8-Feb | 1-Apr |
6 | 1-May | 15-May |
7 | 31-Jan | 1-Jun |
8 | 15-Jun | 18-Jun |
9 | 1-Mar | 1-May |
10 | 1-Jan | 2-Jan |
I have done a sample PBIX file for it, but it does not calculate correctly and below is the link of the report:
https://drive.google.com/file/d/1mo4iqptANkFQ3vr4nV2ASxdNLqMjzwzV/view?usp=sharing
so the count should look likie this:
Month | Count of In Progress |
Jan | 4 |
Feb | 3 |
March | 4 |
April | 4 |
May | 5 |
June | 4 |
I am using the below expression:
Month-Year | Carryover Tickets1 |
20-Apr | 4 |
20-Feb | 2 |
20-Jan | 3 |
20-Jun | 3 |
20-Mar | 3 |
20-May | 4 |
can any one help me how to correct the expression?
Solved! Go to Solution.
Hi @sandip
Create measure
Measure =
VAR min1 =
MIN ( 'Calendar'[Date] )
VAR max1 =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Demo Data'[Ticket ] ),
FILTER (
'Demo Data',
max1 >= [Date Created]
&& ( max1 <= [Date Closed]
|| min1 <= [Date Closed] )
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sandip
Create measure
Measure =
VAR min1 =
MIN ( 'Calendar'[Date] )
VAR max1 =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Demo Data'[Ticket ] ),
FILTER (
'Demo Data',
max1 >= [Date Created]
&& ( max1 <= [Date Closed]
|| min1 <= [Date Closed] )
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your solution. It looks like you have that PBIX file with the propoer solution, can I have it or can you attach it at this forum so that I can go though it?
Thanks,
Sandip
@sandip , refer if this blog can help
or refer if this can help
https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0
Hi,
I have shared a PBIX file , Can you please check what I have wrong there?
I really need this help. Aslo If an employee terminated at the same month of joining, then your below expression will not work and I have tested it:
Below is the data set for reference:
Start Date | End Date | Employee Id |
1/1/2015 0:00 | 1/28/2015 0:00 | 1 |
1/1/2015 0:00 | 1/21/2015 0:00 | 2 |
2/10/2015 0:00 | 12/31/2015 0:00 | 3 |
So can you please give correct expression which will consider the employee number who has resigned at the same month of joining?
Thanks,
Sandip
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |