Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting multiple date columns

Hello all,

 

I'm using PowerBI Desktop and trying to generate an Clustered Column Chart from a CSV file that I receive from an external company.

The CSV looks like:

 

IDDateAddLastModifiedCurrentStatus
18/25/20198/26/2019Closed
28/27/20199/1/2019Closed
39/1/20199/7/2019Closed
49/5/20199/6/2019InProgress
59/5/20199/9/2019InProgress
69/15/20199/15/2019New

 

My goal is to have a Slicer and when I select dates 9/1/2019 and 9/10/2019, it will have on the first column the count of DateAdd (3 in this example) and on the second column the count of LastModified and Status = "Closed" (2 in this example).

I can do it separated but having trouble when trying to get it together.

Can someone help me?

 

Thanks in advice.

Need Help
Show and Tell
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    At first, you need to create a calendar table as a slicer.

    Calendar =
CALENDAR ( MIN ( 'Table'[DateAdd] ), MAX ( 'Table'[LastModified] ) )

    Then create two new measures to get counts.

    Count1 =
VAR minselect =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR maxselect =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[DateAdd] >= minselect
                && 'Table'[DateAdd] <= maxselect
        )
    )
    Count2 =
VAR minselect =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR maxselect =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[LastModified] >= minselect
                && 'Table'[LastModified] <= maxselect
                && 'Table'[CurrentStatus] = "Closed"
        )
    )

    Here is the result.

    I uploaded my test file as a attachment, you can download and refer to it.

     

4 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    At first, you need to create a calendar table as a slicer.

    Calendar =
CALENDAR ( MIN ( 'Table'[DateAdd] ), MAX ( 'Table'[LastModified] ) )

    Then create two new measures to get counts.

    Count1 =
VAR minselect =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR maxselect =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[DateAdd] >= minselect
                && 'Table'[DateAdd] <= maxselect
        )
    )
    Count2 =
VAR minselect =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR maxselect =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[LastModified] >= minselect
                && 'Table'[LastModified] <= maxselect
                && 'Table'[CurrentStatus] = "Closed"
        )
    )

    Here is the result.

    I uploaded my test file as a attachment, you can download and refer to it.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello v-eachen-msft,

       

      Thank you so much for your prompt reply. Your suggestion worked like a charm.

       

      I'm trying to create a Clustered Column chart, grouping by Month/Year, but all I got is a sum on all months.

      Is there anything I should to get the chart?

       

      powerbie1a5cbc8997328a1.png

       

      Thanks!