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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndrePerez
Frequent Visitor

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 @AndrePerez ,

 

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 @AndrePerez ,

 

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.

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 @AndrePerez ,

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.