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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JonV
Helper II
Helper II

DATESBETWEEN with two tables

Hi all,

I have a fact table that has start and end dates for a process. My goal is to create a calculated column in that fact table to determine the number of days in that period that were holidays.

Separately, I have a dim date table that has a column to denote whether a given date is a holiday or not.

What I thought I'd be able to do is use something along the lines of 

 

 

=COUNTROWS(
    CALCULATETABLE(
        DATESBETWEEN(
            'Date Table'[Dates], 'Fact Table'[Start Date], 'Fact Table'[End Date]), 
        'Date Table'[IsHoliday?] = TRUE
    )
)

 

 

The issue I'm running into is the DATESBETWEEN function won't accept values from the Fact Table. The relationship between the two tables is defined in the model. 

Any thoughts on why this isn't working or a better approach?

1 ACCEPTED SOLUTION


@VasTg wrote:

How is the relationship between the fact and date is defined?

It is a one-to-many relationship between the date from the date table to the start date in the fact table. 


@VasTg wrote:

Here is the sample DAX that works for me.

 

 

Column = CALCULATE(SUM('Table 2'[Holiday]),FILTER(ALL('Table 2'),'Table 2'[Date]>='Fact'[Start] &&'Table 2'[Date]<='Fact'[End]))

 

This did not work for me either as DAX is not allowing my to compare the dates from the Fact table to the Date table, I suspect for the same reason as I'm not able to use the functions I intended to.

View solution in original post

5 REPLIES 5
VasTg
Memorable Member
Memorable Member

@JonV 

 

How is the relationship between the fact and date is defined?

 

Here is the sample DAX that works for me.

 

Column = CALCULATE(SUM('Table 2'[Holiday]),FILTER(ALL('Table 2'),'Table 2'[Date]>='Fact'[Start] &&'Table 2'[Date]<='Fact'[End]))

 

Table 2 is date(Holiday is a column with value 1 as holiday and 0 is a non holiday)

Fact is your fact.

 

If this helps, mark it as a solution

Kudos are nice too

 

Connect on LinkedIn

@VasTg 

I was able to get your formula to work with a bit of modification:

Holidays in Period = 
CALCULATE(
    SUM('Date Table'[IsHoliday]),
    FILTER('Date Table',
        'Date Table'[FullDate] >='Fact Table'[Start Date] 
        && 'Date Table'[FullDate] <= 'Fact Table'[End Date]
    )
)

 


@VasTg wrote:

How is the relationship between the fact and date is defined?

It is a one-to-many relationship between the date from the date table to the start date in the fact table. 


@VasTg wrote:

Here is the sample DAX that works for me.

 

 

Column = CALCULATE(SUM('Table 2'[Holiday]),FILTER(ALL('Table 2'),'Table 2'[Date]>='Fact'[Start] &&'Table 2'[Date]<='Fact'[End]))

 

This did not work for me either as DAX is not allowing my to compare the dates from the Fact table to the Date table, I suspect for the same reason as I'm not able to use the functions I intended to.

VasTg
Memorable Member
Memorable Member

@JonV 

 

Try variables to get the start and end date from fact.

 

Attach your mockup pbix to troubleshoot.

 

If this helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn

Here is a link to a sample pbix. Please let me know if that works.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors