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.