Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

*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 ...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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 NameCount # of date occurances
Created23
Resolved43
[CHART] Date of First Response37
Updated44
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_TypeKeyStatusResolutionCreatedUpdatedResolved[CHART] Date of First Response
StorySTR-458To Do 2/17/22 12:052/17/22 12:57 2/17/22 12:57
EpicSTR-143In Progress 12/21/21 8:522/17/22 12:05  
StorySTR-150DoneDone12/9/21 9:562/17/22 12:041/11/22 10:1112/15/21 7:10
StorySTR-151DoneDone12/23/21 15:102/17/22 12:041/7/22 9:2912/24/21 3:22
StorySTR-147DoneDone12/13/212/17/221/31/22 9:0212/13/21
StoryPAY-101To Do 7/14/21 14:182/17/22 10:41  
StoryPAY-180Blocked 11/19/21 14:392/17/22 10:32 12/15/21 11:19
StoryPAY-253In Progress 2/11/22 15:162/17/22 10:23 2/17/22 7:47
StorySTR-406In Progress 2/11/22 1:072/17/22 8:46  
StoryERP-438In Progress 2/10/22 10:332/17/22 8:39  
StoryPAY-96In Progress 7/14/21 14:172/17/22 7:50  
StoryPAY-256UAT 2/14/22 11:262/17/22 7:50  
StorySTR-360In Progress 2/7/22 9:392/17/22 6:29 2/17/22 6:16
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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?