Search code examples
ssasdaxtabularbism

Next available Date

I have a tubular model that has a standard star schema On my dim date table there is a column that flags UK holidays I would like to not included this date if a key chooses a date that has been flagged but the next availble date I don't have much access to the database to build a function for this as Ive seen others do

Could anyone suggest some Dax or a method of doing this

Thanks so much in advance

sample enter image description here

Solution

  • You can create a calculated column to get the next working dateKey if date is flagged as non working date. In case date is not flagged the column contains the dateKey value.

    Use this DAX expression in the calculated column:

    =
IF (
    [isDefaultCalendarNonWorkingDay] = 1,
    CALCULATE (
        MIN ( [dateKey] ),
        FILTER (
            DimDate,
            [dateKey] > EARLIER ( [dateKey] )
                && [isDefaultCalendarNonWorkingDay] = 0
        )
    ),
    [dateKey]
)

    I've recreated you DimDate table with some sample data:

    enter image description here

    Let me know if this helps.