Next available Date

Question

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

@ alejandro zuleta `SELECT [dimId] ,[dateKey] ,[date] ,[year] ,[month] ,[day] ,[monthName] ,[isDefaultCalendarNonWorkingDay] FROM [dbo].[vw_DIM_Date] WHERE datekey in (20160101,20160102, 20160103) ` — user2181700, Sep 13 '16 at 18:03
So the table in dax will look the same I would like to select 2016-01-02 because there is a flag against the previous. basically this is not selecting a certain date or weekend. So may a submission came in on the weekend but you want tag it with the day it will be submitted which is the next working day — user2181700, Sep 13 '16 at 18:17
Where do you want to exclude NonWorking dates? Do you want to prevent the user select any NonWorking dates from a filter? — alejandro zuleta, Sep 13 '16 at 19:28
It's more of a calculated column I'm after It will utilize date where it's nonWorkingday is 0 but when it is 1 then select the next date that is nonWorkingday is 0 So in the sample above the calculation against datekey 20160101 would actually display column Caldate *2016-01-02* (Caldate is the new calculated column) @alejandrozuleta — user2181700, Sep 13 '16 at 20:59
In that case, you can use a DAX expression to create a calculate column populated only with working dates. i.e if date is 2016-01-01 and is flagged with 1 then should put the not flagged next date. Let me know If that is what you are after, I'll answer with a possible solution. — alejandro zuleta, Sep 13 '16 at 21:07
@alejandrozuleta okay I'm with you I was trying to do this but got stuck when you have weekend and it could be two NonWorking days in a row. I'm still new to DAX it would be very helpful to understand how an expression could iterate through and pick up the right value , in this case the next working date — user2181700, Sep 13 '16 at 21:11
In that case, saturday and sunday dates will be flagged? or only the first date? — alejandro zuleta, Sep 13 '16 at 21:23

score 0 · Accepted Answer · answered Sep 14 '16 at 01:05

0

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:

Let me know if this helps.

answered Sep 14 '16 at 01:05

alejandro zuleta

13,962
3
28
48

That is exactly what I was trying to achieve! I think I dabbled a little with the EARLIER Function but didn't use it correctly. Thank you very much @alejandro zuleta in having patiences with me being a novice. This is the type of comradery that needed on StackOverflow :-) – user2181700 Sep 14 '16 at 02:06
@user2181700, you are welcome. EARLIER is a very helpful function while calculating columns. – alejandro zuleta Sep 14 '16 at 02:10

Next available Date

1 Answers1