Forum Discussion
Helper IHow to count (SUM) dates in several columns
I have a table that contains 4 columns with scheduled dates in all of the columns. I need to count how many times an OG has a a date scheduled before a timeframe. I have over 200 rows and 12 OG's. When looking at this example and counting all instances for one OG before 5/1/2017. the result would be:
Milwaukee Operations 16
MInneapolis 7
I am new at Power BI and can not figure this out. A measure or calculated column will do. Thanks in advance!
| OG | Schedule.1 | Schedule.2 | Schedule.3 | Schedule.4 |
| Milwaukee Operations | 1/12/2017 | 4/20/2017 | 7/20/2017 | |
| Milwaukee Operations | 1/12/2017 | 4/20/2017 | 7/20/2017 | |
| Milwaukee Operations | 1/12/2017 | 4/20/2017 | 7/20/2017 | |
| Milwaukee Operations | 1/19/2017 | 4/4/2017 | 7/13/2017 | |
| Milwaukee Operations | 1/19/2017 | 4/4/2017 | ||
| Milwaukee Operations | 1/19/2017 | 4/4/2017 | 4/25/2017 | |
| Milwaukee Operations | 1/19/2017 | 4/4/2017 | ||
| Milwaukee Operations | 1/30/2017 | 6/1/2017 | ||
| Minneapolis Operations | 2/2/2017 | 8/1/2017 | ||
| Minneapolis Operations | 2/2/2017 | 3/1/2017 | 4/5/2017 | 5/1/2017 |
| Minneapolis Operations | 2/7/2017 | 3/1/2017 | 4/1/2017 | 9/1/2017 |
I assume you want the cutoff date to be dynamic?
One way of doing it is to use a 'disconnected slicer'. There are lots of good resources out there about this if you want to read up on it (e.g. http://exceleratorbi.com.au/wp-content/uploads/2017/02/SQLSat_MattAllington_DisconnectedTablesInDAX.pdf )
Here are the steps:
1. Bring in your data into a new .pbix file
2. Create a calendar table in the file as well. It should have a 'Dates' field with all of the dates that you might want to use to define your timeframe. (There are several ways to do this, but you could just click on the New Table button and copy and paste the data in). Make sure there is no relationship between the data table and the Calendar table
3. Create these measures (your table name may differ):
Date Filter = MAX('Calendar'[Dates]) Sch 1 Count = CALCULATE(COUNT(Table1[Schedule.1]), FILTER(Table1, Table1[Schedule.1] <= [Date Filter])) Sch 2 Count = CALCULATE(COUNT(Table1[Schedule.2]), FILTER(Table1, Table1[Schedule.2] <= [Date Filter])) Sch 3 Count = CALCULATE(COUNT(Table1[Schedule.3]), FILTER(Table1, Table1[Schedule.3] <= [Date Filter])) Sch 4 Count = CALCULATE(COUNT(Table1[Schedule.4]), FILTER(Table1, Table1[Schedule.4] <= [Date Filter])) Total Count = [Sch 1 Count] + [Sch 2 Count] + [Sch 3 Count] + [Sch 4 Count]Then add a filter using your Calendar[Dates] field.
Also add a table that includes the OC field and the Total Count field.
3 Replies
MalSResolver III
I assume you want the cutoff date to be dynamic?
One way of doing it is to use a 'disconnected slicer'. There are lots of good resources out there about this if you want to read up on it (e.g. http://exceleratorbi.com.au/wp-content/uploads/2017/02/SQLSat_MattAllington_DisconnectedTablesInDAX.pdf )
Here are the steps:
1. Bring in your data into a new .pbix file
2. Create a calendar table in the file as well. It should have a 'Dates' field with all of the dates that you might want to use to define your timeframe. (There are several ways to do this, but you could just click on the New Table button and copy and paste the data in). Make sure there is no relationship between the data table and the Calendar table
3. Create these measures (your table name may differ):
Date Filter = MAX('Calendar'[Dates]) Sch 1 Count = CALCULATE(COUNT(Table1[Schedule.1]), FILTER(Table1, Table1[Schedule.1] <= [Date Filter])) Sch 2 Count = CALCULATE(COUNT(Table1[Schedule.2]), FILTER(Table1, Table1[Schedule.2] <= [Date Filter])) Sch 3 Count = CALCULATE(COUNT(Table1[Schedule.3]), FILTER(Table1, Table1[Schedule.3] <= [Date Filter])) Sch 4 Count = CALCULATE(COUNT(Table1[Schedule.4]), FILTER(Table1, Table1[Schedule.4] <= [Date Filter])) Total Count = [Sch 1 Count] + [Sch 2 Count] + [Sch 3 Count] + [Sch 4 Count]Then add a filter using your Calendar[Dates] field.
Also add a table that includes the OC field and the Total Count field.