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 

 

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