cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YycPBI Visitor
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 v-yuezhe-msft
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")

1.PNG

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.

 1.PNG

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
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 v-yuezhe-msft
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")

1.PNG

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.

 1.PNG

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
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

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 96 members 1,469 guests
Please welcome our newest community members: