Forum Discussion

JonV's avatar
JonV
Icon for Helper II rankHelper II
6 years ago
Solved

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....
  • JonV's avatar
    JonV
    6 years ago
    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's avatar
VasTg
Icon for Memorable Member rankMemorable Member
6 years ago

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

 

  • JonV's avatar
    JonV
    Icon for Helper II rankHelper II
    6 years ago
    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's avatar
      VasTg
      Icon for Memorable Member rankMemorable Member
      6 years ago

      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

  • JonV's avatar
    JonV
    Icon for Helper II rankHelper II
    6 years ago

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