*HELP* How can I count date range occurrence with multiple columns?

[Anonymous]

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

[Vera_33]

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

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

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?  

[Anonymous]

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? 

[Vera_33]

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