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 ,

 

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.