cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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.

 

Many thanks for your help 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
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])
                    )
                )
            )    
        

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Highlighted
Helper II
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.

 

Many thanks for your help 

Highlighted
Microsoft
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])
                    )
                )
            )    
        

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
Helper II
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
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]) ) ) )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Super User V
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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Super User II
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors