Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
domdom
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
Phil_Seamark
Employee
Employee

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
Ashish_Mathur
Super User
Super User

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/
Phil_Seamark
Employee
Employee

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!

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

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

Proud to be a Datanaut!

domdom
Helper II
Helper II

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.