Forum Discussion
*HELP* How can I count date range occurrence with multiple columns?
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.
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 |
Thank you for the reply. So, I am new to this forum and do not see a way to upload file here. Can you tell me where I can upload that or send it to?
Hi Anonymous
You can paste some sample data, normally people share via one drive, or other ways...I guess not everyone has the option to upload file for now. Also pm you my email address if you would like to send over sample file
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.
So this is close. Issue is date filter. As you can see I have a static filter that does this already but what I need is a date filter. for range start and range end. Any thoughts?