Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dax Help - Work out custom days between

Hi,

 

I have a calendar table with the following fields

 

Date      |         Index

 

 

I then have a table called Holidays with the fields

 

From     |        To

 

There are inactive relationships between the from and to in the holiday table to the date field in the calendar table.

 

How would I go about calculateing the days between using the index field for 'to' - index field from'from'.

 

Thanks

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    What does your data look like? Please check out the demo in the attachment.

    It could be a calculated column like this.

    days =
CALCULATE (
    SUM ( 'Calendar'[Index] ),
    FILTER ( 'Calendar', 'Calendar'[Date] >= [From] && 'Calendar'[Date] <= [To] )
)

    Best Regards,

    Dale

4 Replies

  • anandav's avatar
    anandav
    Icon for Skilled Sharer rankSkilled Sharer
    8 years ago

    HI Anonymous,

    Have you tried creating a new column in Holiday table?:

    Column = DATEDIFF(Table3[From], Table3[To], DAY)

     

    Or a measure:

    Measure = DATEDIFF(MIN(Table3[Column1]), MIN(Table3[Column2]),DAY)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Anand,

       

      I wont be able to use that as I need to exclude weekends ands bank holidays.

       

      Thanks

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        What does your data look like? Please check out the demo in the attachment.

        It could be a calculated column like this.

        days =
CALCULATE (
    SUM ( 'Calendar'[Index] ),
    FILTER ( 'Calendar', 'Calendar'[Date] >= [From] && 'Calendar'[Date] <= [To] )
)

        Best Regards,

        Dale