Dax Help - Work out custom days between

[Anonymous]

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

[anandav]

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]

Hi @Anand,

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

Thanks

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

Thanks Dale will have a look into that.