Forum Discussion
*HELP* How can I count date range occurrence with multiple columns?
I am newbie to power Bi and I am trying to do a DATE RANGE count with multiple date count columns and I am sooooooooo LOST on how to solve my reporting need. I have been on this for past 16 hours in total and have yet to find an answer that will work for me. That said, I am hoping to find kind soul who might be able to guid me thru this porblem. Thanks in advance, to the kind soul who many come to my rescue. ~:0)
Summary of what I am trying to do.
1. Create calendar table
2. Calendar table would need to pull *max* and *min* values dynamiclly from any of these three columns found in a projects table.
| Resolution | Created | Updated | Resolved | [CHART] Date of First Response |
3. Report would display monthly counts for custom range that would start at the begining of the month thru the end of the month.
| 01/01/2022 thru 01/31/2022 | |
| Created | 23 |
| Resolved | 43 |
| [CHART] Date of First Response | 37 |
| Updated | 44 |
| Total Touches: Sum of Created + Resolved + [CHART] Date of First Response, Updated (No Blanks, 0) | 147 |
Hi Anonymous ,
To reach that, a table with one column contains "updated","created"... is necessary. like the following:
Then create a measure via this code:
Measure = VAR _Created = COUNTROWS ( FILTER ( 'Table', [Created] <> BLANK () ) ) VAR _Resolved = COUNTROWS ( FILTER ( 'Table', [Resolved] <> BLANK () ) ) VAR _Updated = COUNTROWS ( FILTER ( 'Table', [Updated] <> BLANK () ) ) VAR _CHART_Date_of_First_Response = COUNTROWS ( FILTER ( 'Table', [[CHART]] Date of First Response] <> BLANK () ) ) RETURN IF ( HASONEVALUE ( 'Project Tab Column Name'[Project Tab Column Name] ), SWITCH ( SELECTEDVALUE ( 'Project Tab Column Name'[Project Tab Column Name] ), "Created", _Created, "Resolved", _Resolved, "[CHART] Date of First Response", _CHART_Date_of_First_Response, "Updated", _Updated ), _CHART_Date_of_First_Response + _Created + _Resolved + _Updated )So, the result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Anonymous
You need to provide sample data (in a format people can copy) in 2. table, what data is in these 3 columns? Then expected results in 3. , where is the range 01/01/2022 thru 01/31/2022 coming from? Any logic to calculate the range is 23?
Resolution Created Updated Resolved [CHART] Date of First Response Sorry maybe this is more helpful, in this format. If not please let me know.
Need a custom filter that will all me to sort by date ranges fist of month to end of month like this
01/01/2022 thru 01/31/2022 Project Tab Column Name Count # of date occurances Created 23 Resolved 43 [CHART] Date of First Response 37 Updated 44 Total In Scope: Sum of Created + Resolved + [CHART] Date of First Response, Updated (No Blanks, 0) 147 Project Data Tab Information with date columns needed
Issue_Type Key Status Resolution Created Updated Resolved [CHART] Date of First Response Story STR-458 To Do 2/17/22 12:05 2/17/22 12:57 2/17/22 12:57 Epic STR-143 In Progress 12/21/21 8:52 2/17/22 12:05 Story STR-150 Done Done 12/9/21 9:56 2/17/22 12:04 1/11/22 10:11 12/15/21 7:10 Story STR-151 Done Done 12/23/21 15:10 2/17/22 12:04 1/7/22 9:29 12/24/21 3:22 Story STR-147 Done Done 12/13/21 2/17/22 1/31/22 9:02 12/13/21 Story PAY-101 To Do 7/14/21 14:18 2/17/22 10:41 Story PAY-180 Blocked 11/19/21 14:39 2/17/22 10:32 12/15/21 11:19 Story PAY-253 In Progress 2/11/22 15:16 2/17/22 10:23 2/17/22 7:47 Story STR-406 In Progress 2/11/22 1:07 2/17/22 8:46 Story ERP-438 In Progress 2/10/22 10:33 2/17/22 8:39 Story PAY-96 In Progress 7/14/21 14:17 2/17/22 7:50 Story PAY-256 UAT 2/14/22 11:26 2/17/22 7:50 Story STR-360 In Progress 2/7/22 9:39 2/17/22 6:29 2/17/22 6:16 - v-chenwuz-msft
Community Support
Hi Anonymous ,
To reach that, a table with one column contains "updated","created"... is necessary. like the following:
Then create a measure via this code:
Measure = VAR _Created = COUNTROWS ( FILTER ( 'Table', [Created] <> BLANK () ) ) VAR _Resolved = COUNTROWS ( FILTER ( 'Table', [Resolved] <> BLANK () ) ) VAR _Updated = COUNTROWS ( FILTER ( 'Table', [Updated] <> BLANK () ) ) VAR _CHART_Date_of_First_Response = COUNTROWS ( FILTER ( 'Table', [[CHART]] Date of First Response] <> BLANK () ) ) RETURN IF ( HASONEVALUE ( 'Project Tab Column Name'[Project Tab Column Name] ), SWITCH ( SELECTEDVALUE ( 'Project Tab Column Name'[Project Tab Column Name] ), "Created", _Created, "Resolved", _Resolved, "[CHART] Date of First Response", _CHART_Date_of_First_Response, "Updated", _Updated ), _CHART_Date_of_First_Response + _Created + _Resolved + _Updated )So, the result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.