cancel
Showing results for
Search instead for
Did you mean:
Visitor

## YTD Count Based on Two Text Columns and a non-continuous date

Hello PBI Community,

How do you calculate YTD counts based on two text columns (Reported By and Category) with a non-continous Date Time column in a single Table (call it Table 1)?  The Reported By column contains more than >500 names with multiple entries with the same name and the Category column has ~10 different categories that also have mulitple entries.  Here is an example table:

Table 1:

Date Time | Reported By | Category

2010-03-16 9:00:00 AM | John | Cat A

2010-07-16 8:00:00 AM | Mike | Cat A

2011-10-18 12:00:00 PM | Mike | Cat B

2012-12-29 9:00:00 AM | David | Cat C

The goal is to create a matrix or table on the PBI Dashboard that displays a count of each category each person had reported (that is filtered by a slicer) along with a YTD column for each category reported.  For example:

Reported By | Cat A | Cat B | Cat C | Total | YTD Cat A | YTD Cat B | YTD Cat C |

Your help would be greatly appreciated!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator

## Re: YTD Count Based on Two Text Columns and a non-continuous date

@YycPBI,

Please firstly create the following column in your table.

`Date = Table1[Date Time].[Date]`

Then create a calendar table using dax below.

`Date = ADDCOLUMNS(CALENDAR("2010-01-01","2013-12-31"),"Year",YEAR([Date]),"Month",MONTH([Date]))`

At last, create the following measures in your table.

`count_A = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat A")`
`count_B = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat B")`
`count_C = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat C")`
`YTD_A = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat A")`
`YTD_B = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat B")`
`YTD_C = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat C")`

Another method is directly to create YTD measure using DAX: YTD = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date]). You can check more details in attached PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: YTD Count Based on Two Text Columns and a non-continuous date

Hi,

Here's my suggestion.

1.  Seperate Date and Time in 2 columns and remove the time column if it is not required
2.  Create a Calendar Table with consecutive dates and build a relationship from the Date column of your base data table to the Date column of the Calendar Table.
3.  In the Calendar Table, create 2 calculated column to extract Year and Month.  Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
4.  In your Table/Matrix visual, drag Reported By and Category to the Row labels
5.  Create 2 slicers - one for Year and anothe for month.  Select any one month and Year
6.  Write these measures

COUNT = COUNTROWS(Data)

YTD Count = CALCULATE([Count],DATESYTD(Calendar[Date]))

Hope this helps.

2 REPLIES 2
Moderator

## Re: YTD Count Based on Two Text Columns and a non-continuous date

@YycPBI,

Please firstly create the following column in your table.

`Date = Table1[Date Time].[Date]`

Then create a calendar table using dax below.

`Date = ADDCOLUMNS(CALENDAR("2010-01-01","2013-12-31"),"Year",YEAR([Date]),"Month",MONTH([Date]))`

At last, create the following measures in your table.

`count_A = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat A")`
`count_B = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat B")`
`count_C = CALCULATE(COUNTA(Table1[Reported By]),Table1[Category]="Cat C")`
`YTD_A = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat A")`
`YTD_B = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat B")`
`YTD_C = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date],Table1[Category]="Cat C")`

Another method is directly to create YTD measure using DAX: YTD = TOTALYTD(COUNTA(Table1[Reported By]),'Date'[Date]). You can check more details in attached PBIX file.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: YTD Count Based on Two Text Columns and a non-continuous date

Hi,

Here's my suggestion.

1.  Seperate Date and Time in 2 columns and remove the time column if it is not required
2.  Create a Calendar Table with consecutive dates and build a relationship from the Date column of your base data table to the Date column of the Calendar Table.
3.  In the Calendar Table, create 2 calculated column to extract Year and Month.  Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
4.  In your Table/Matrix visual, drag Reported By and Category to the Row labels
5.  Create 2 slicers - one for Year and anothe for month.  Select any one month and Year
6.  Write these measures

COUNT = COUNTROWS(Data)

YTD Count = CALCULATE([Count],DATESYTD(Calendar[Date]))

Hope this helps.

## Helpful resources

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 96 members 1,469 guests
Recent signins:
Please welcome our newest community members: