Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.
1-1.PNG

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
v-eachen-msft
Community Support
Community Support

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.
1-1.PNG

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

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!

Hi @Anonymous ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors