Forum Discussion
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!
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,
LydiaHi,
Here's my suggestion.
- Seperate Date and Time in 2 columns and remove the time column if it is not required
- 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.
- In the Calendar Table, create 2 calculated column to extract Year and Month. Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
- In your Table/Matrix visual, drag Reported By and Category to the Row labels
- Create 2 slicers - one for Year and anothe for month. Select any one month and Year
- Write these measures
COUNT = COUNTROWS(Data)
YTD Count = CALCULATE([Count],DATESYTD(Calendar[Date]))
Hope this helps.
2 Replies
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
Super User
Hi,
Here's my suggestion.
- Seperate Date and Time in 2 columns and remove the time column if it is not required
- 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.
- In the Calendar Table, create 2 calculated column to extract Year and Month. Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
- In your Table/Matrix visual, drag Reported By and Category to the Row labels
- Create 2 slicers - one for Year and anothe for month. Select any one month and Year
- Write these measures
COUNT = COUNTROWS(Data)
YTD Count = CALCULATE([Count],DATESYTD(Calendar[Date]))
Hope this helps.