cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Calculating the total days that fall in various months - based on a set of date range pairings

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Calculating the total days that fall in various months - based on a set of date range pairings

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])
)
)
)
```

Proud to be a Datanaut!

7 REPLIES 7
Highlighted
Helper II

## DAX - calculating the total days falling in months from a range of date pairs

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.

Highlighted
Microsoft

## Re: Calculating the total days that fall in various months - based on a set of date range pairings

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])
)
)
)
```

Proud to be a Datanaut!

Highlighted
Helper II

## Re: Calculating the total days that fall in various months - based on a set of date range pairings

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

Highlighted
Microsoft

## Re: Calculating the total days that fall in various months - based on a set of date range pairings

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])
)
)
)
```

Proud to be a Datanaut!

Highlighted
Super User V

## Re: Calculating the total days that fall in various months - based on a set of date range pairings

Hi,

For January 2018, the answer should be 59.  You may refer to my solution in this file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Super User II

## Re: DAX - calculating the total days falling in months from a range of date pairs

@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!

Highlighted
Helper II

## Re: DAX - calculating the total days falling in months from a range of date pairs

I've done all the things you've mentioned in my question