Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I have two tables - table A and table B.
----------
TABLE A - a set of records showing a date range (start and end dates) (about 20,000 records)
----------
id date start date end
1 01/01/2018 10/02/2018
2 10/01/2018 20/01/2018
3 15/01/2018 03/02/2018
4 05/02/2018 20/03/2018
5 26/02/2018 04/03/2018
6 10/03/2018 15/03/2018
----------
TABLE B - a table of months - showing the date of the first and last day of the month (about 10 years worth of months)
----------
month start month end
01/01/2018 31/01/2018
01/02/2018 28/02/2018
01/03/2018 31/03/2018
I would like to add a new column to table B which calculates the sum of all days that fall between the month start/month end for every start/end date pair record in Table A. E.g. If we just take Jan 2018 in Table B. In table A - id 1 has 31 days in Jan, id 2 has 10 days, id 3 has 16 days and id's 4,5 and 6 all have no days in Jan. So the formula result should show as 57 days next to our jan dates in table B. And this should be repeated for all other months in Table B.
I hope that makes sense? I will have about 10 years worth of data - so I am looking for a DAX formula that doesnt involve me creating additional columns in Table A - which will be unsustainable.
Many thanks for your help
Solved! Go to Solution.
HI @domdom
This calculated column on TableB gets pretty close. I've also uploaded a PBIX file for you to check.
New Column = COUNTROWS( GENERATE( CALENDAR( MIN('TableB'[month start]), MAX('TableB'[month end]) ) , FILTER( TableA, [Date] >= [date start] && [Date] <= [date end] && [Date] >= EARLIER(TableB[month start]) && [Date] < EARLIER('TableB'[month end]) ) ) )
Hi,
For January 2018, the answer should be 59. You may refer to my solution in this file.
Hope this helps.
HI @domdom
This calculated column on TableB gets pretty close. I've also uploaded a PBIX file for you to check.
New Column = COUNTROWS( GENERATE( CALENDAR( MIN('TableB'[month start]), MAX('TableB'[month end]) ) , FILTER( TableA, [Date] >= [date start] && [Date] <= [date end] && [Date] >= EARLIER(TableB[month start]) && [Date] < EARLIER('TableB'[month end]) ) ) )
Thanks Phil - you've literally saved me hours 🙂 I could do it in SQL but don't know DAX that well.
One last thing - I actaully need to sum only working days that fall between the date ranges (e.g. not saturday and sunday) - but didn't want to overcomplicate my initial question.
Is there an easy change to your solution to achieve this? in excel there is workdays() formula - but im not sure a similar one exists in Dax....
Thanks again appreciate it
Hi @domdom
Just wrap a FILTER around the first GENERATE function as follows. It doesn' t take into account Public Holidays.
New Column = COUNTROWS( GENERATE( FILTER( CALENDAR( MIN('TableB'[month start]), MAX('TableB'[month end]) ), WEEKDAY([Date],2) < 6)
, FILTER( TableA, [Date] >= [date start] && [Date] <= [date end] && [Date] >= EARLIER(TableB[month start]) && [Date] < EARLIER('TableB'[month end]) ) ) )
Hi there,
I have two tables - table A and table B.
----------
TABLE A - a set of records showing a date range (start and end dates) (about 20,000 records)
----------
id date start date end
1 01/01/2018 10/02/2018
2 10/01/2018 20/01/2018
3 15/01/2018 03/02/2018
4 05/02/2018 20/03/2018
5 26/02/2018 04/03/2018
6 10/03/2018 15/03/2018
----------
TABLE B - a table of months - showing the date of the first and last day of the month (about 10 years worth of months)
----------
month start month end
01/01/2018 31/01/2018
01/02/2018 28/02/2018
01/03/2018 31/03/2018
I would like to add a new column to table B which calculates the sum of all days that fall between the month start/month end for every start/end date pair record in Table A. E.g. If we just take Jan 2018 in Table B. In table A - id 1 has 31 days in Jan, id 2 has 10 days, id 3 has 16 days and id's 4,5 and 6 all have no days in Jan. So the formula result should show as 57 days next to our jan dates in table B. And this should be repeated for all other months in Table B.
I hope that makes sense? I will have about 10 years worth of data - so I am looking for a formula that doesnt involve me creating additional columns in Table A - which will be unsustainable.
Many thanks for your help
@domdom can you give an example of what you expected result should look like, what are you actually trying to achieve what is the reason for doing this? understanding helps to give thet best solution
Proud to be a Super User!
I've done all the things you've mentioned in my question
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |