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 all,
I have a table that looks like this:
Application ID | Date Created | Date Resolved | Status |
001 | 01/01/2019 | 01/05/2019 | Approved |
002 | 01/03/2019 | 01/07/2019 | Rejected |
003 | 01/09/2019 | Pending |
I would like to create a table that counts the number of applications that are in progress for each date, alongside a count of applications created or resolved on that date. For example:
Calendar Date | Created | Resolved | In Progress |
01/01/2019 | 1 | 0 | 0 |
01/02/2019 | 0 | 0 | 1 |
01/03/2019 | 1 | 0 | 1 |
01/04/2019 | 0 | 0 | 2 |
01/05/2019 | 0 | 1 | 1 |
01/06/2019 | 0 | 0 | 1 |
01/07/2019 | 0 | 1 | 0 |
01/08/2019 | 0 | 0 | 0 |
01/09/2019 | 1 | 0 | 0 |
Would you have a solution?
Solved! Go to Solution.
Hi @v-piga-msft ,
I figured it out. Thanks again for pointing me in the right direction.
Solution:
In the Date table I created the following measures and columns:
Created Count = CALCULATE(COUNT('Table'[CreatedDate]),FILTER('Table','Table'[CreatedDate ]='Date'[Date]))
Resolved Count = CALCULATE(COUNT('Table'[Date_Resolved]),FILTER(Table','Table'[Date_Resolved]='Date'[Date]))
In Progress = CALCULATE( COUNT('Table'[CreatedDate]), FILTER( 'Table', 'Table'[CreatedDate]<'Date'[Date] && 'Table'[Date_Resolved]>'Date'[Date] ) )
The final result as a table is as desired:
Date | Created Count | Resolved Count | In Progress |
7/31/2019 | 193 | 515 | 15,904 |
7/30/2019 | 225 | 579 | 16,195 |
7/29/2019 | 195 | 530 | 16,578 |
7/28/2019 | 148 | 40 | 16,960 |
Cheers,
Hi @des_san ,
You could create the two calculated column like below in calendar table to achieve the Created and Resolved. And then create the measure with the dax below.
Created = IF ( LOOKUPVALUE ( 'Table'[Date Created], 'Table'[Date Created], 'calendar'[Date] ) > 0, 1, 0 ) Resloved = IF ( LOOKUPVALUE ( 'Table'[Date Resolved], 'Table'[Date Resolved], 'calendar'[Date] ) > 0, 1, 0 ) In Progress = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Date Created] < MAX ( 'calendar'[Date] ) && 'Table'[Date Resolved] > MAX ( 'calendar'[Date] ) ) )
Here is the output.
Best Regards,
Cherry
Hi @v-piga-msft ,
Thank you, your response is very helpful. However, I noticed that if there are multiple created/resolved applications on a particular day the calculated columns aren't accounting for them. I'm only getting 1. Do you have any ideas that might help count for multiple apps in a day?
Best,
Hi @des_san ,
I'm a little confused about your requirement.
If it is convenient, could you describe your requirement in details and share the output you desired so that we could help further on it.
Best Regards,
Cherry
Hi @v-piga-msft ,
I figured it out. Thanks again for pointing me in the right direction.
Solution:
In the Date table I created the following measures and columns:
Created Count = CALCULATE(COUNT('Table'[CreatedDate]),FILTER('Table','Table'[CreatedDate ]='Date'[Date]))
Resolved Count = CALCULATE(COUNT('Table'[Date_Resolved]),FILTER(Table','Table'[Date_Resolved]='Date'[Date]))
In Progress = CALCULATE( COUNT('Table'[CreatedDate]), FILTER( 'Table', 'Table'[CreatedDate]<'Date'[Date] && 'Table'[Date_Resolved]>'Date'[Date] ) )
The final result as a table is as desired:
Date | Created Count | Resolved Count | In Progress |
7/31/2019 | 193 | 515 | 15,904 |
7/30/2019 | 225 | 579 | 16,195 |
7/29/2019 | 195 | 530 | 16,578 |
7/28/2019 | 148 | 40 | 16,960 |
Cheers,
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 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |