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

[Anonymous]

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!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

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

[Ashish_Mathur · Accepted solution]

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.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

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