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:   ID DateAdd LastModifie...
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.

     

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!

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

    Hi Anonymous ,

     

    You can use ALLEXCEPT() funtion to group by date.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello v-eachen-msft ,

       

      Thanks for your help. This is what I've done and it is working for me:

       

      • Created a Calendar Table having minimum as the first entry from DateAdd and maximum as today:
      Calendar = CALENDAR ( MIN ( Table[DateAdd]), TODAY() )
      • Created a New Group from Date column on Calendar Table, grouping by 1 month - Date (bins).
      • Created 2 Groups from DateAdd and LastModified, grouping by 1 month - DateAdd (bins) and LastModified (bins).
      • On my Table, set fields DateAdd (bins) and LastModified (bins) to format MMMM yyyy.
      • On Calendar Table, set both fields to format MMMM yyyy.
      • Created 2 Relationships:

       

      From: Table (DateAdded (bins))
To: Calendar (Date)
Cardinality: Many to one (*:1)
Cross filter direction: Single
Active: yes
      From: Table (LastModified (bins))
To: Calendar (Date)
Cardinality: Many to one (*:1)
Cross filter direction: Single
Active: no

       

       

      • Created two Measures as follows:

       

      CountCreated = 
VAR minselect1 =
    CALCULATE ( MIN ( Calendar[Date (bins)] ), ALLSELECTED ( Calendar[Date (bins)] ) )
VAR maxselect1 =
    CALCULATE ( MAX ( Calendar[Date (bins)] ), ALLSELECTED ( Calendar[Date (bins)] ) )
RETURN
CALCULATE(
    COUNTROWS (
        FILTER (
            Table,
            Table[DateAdd (bins)] >= minselect1
                && Table[DateAdd (bins)] <= maxselect1
        )
    ),
    USERELATIONSHIP('Calendar'[Date], Table[DateAdd (bins)]))
      CountClosed = 
VAR minselect =
    CALCULATE ( MIN ( 'Calendar'[Date (bins)] ), ALLSELECTED ( 'Calendar'[Date (bins)] ) )
VAR maxselect =
    CALCULATE ( MAX ( 'Calendar'[Date (bins)] ), ALLSELECTED ( 'Calendar'[Date (bins)] ) )
RETURN
CALCULATE(
    COUNTROWS (
        FILTER (
            Table,
            Table[LastModified (bins)] >= minselect
                && Table[LastModified (bins)] <= maxselect
                && Changes[CurrentStatus] = "Closed"
        )
    ),
    USERELATIONSHIP('Calendar'[Date], Table[LastModified (bins)]))

       

      To have the Clustered Column Chart, I added Date (bins) from Calendar Table on Axis and CountCreated and CountClosed on Value.