In Calculated Column show the first Wednesday of the current month as default

[rschaudhr]

I have a date column. I want to create another calculated date column where whenever it shows the first Wednesday in the current month and it will say "Default Day" otherwise it will show date from the date column. Is this possible?

I was using the following calculated column measure:

Default Date =

VAR a = 'SQL Results (3)'[DATE_]

VAR y = FILTER(

ALL('SQL Results (3)'[DATE_]),

YEAR('SQL Results (3)'[DATE_]) = YEAR(a) &&

MONTH('SQL Results (3)'[DATE_]) = MONTH(a) &&

DAY([DATE_]) > 1 &&

DAY([DATE_]) < 7 &&

WEEKDAY([DATE_],3) = 2)

Var Wed = format(MAXX(y,'SQL Results (3)'[DATE_]) = 'SQL Results (3)'[DATE_],true)

Var CurrentMonth = format(MONTH('SQL Results (3)'[DATE_])=Month(NOW()),True)

Return

if(CurrentMonth = "True" || Wed = "True", "Current",'SQL Results (3)'[DATE_])

[Anonymous]

Hello,

I need help as well, I used the fomula you guys supplied here, but its not working. Please help.

Default Date =

VAR cur_day = NOW ()

VAR first_wes =

    CALCULATE (

        SELECTEDVALUE (NB_CALENDAR[REPORT_DATE]),

    FILTER (

            ALL (NB_CALENDAR[REPORT_DATE]),

            YEAR (NB_CALENDAR[REPORT_DATE]) = YEAR ( cur_day )

                && MONTH (NB_CALENDAR[REPORT_DATE]) = MONTH ( cur_day )

                && DAY ( NB_CALENDAR[REPORT_DATE]) >= 1

                && DAY ( NB_CALENDAR[REPORT_DATE]) <= 7

                && WEEKDAY ( NB_CALENDAR[REPORT_DATE], 3 ) = 2

            )

            )

RETURN

    CONVERT (

        IF (first_wes = SELECTEDVALUE(NB_ACCOUNT_SUMMARY[REPORT_DATE]),

            "Default Day",

            SELECTEDVALUE(NB_CALENDAR[REPORT_DATE])

            ),

        STRING

    )Highlighted is the first Wednesday of the current month and It should show first_wed but it is showing date rather.

[v-cazheng-msft · Accepted solution]

Hi, @rschaudhr 

You can create a Calculated column as following.

Default Date =

VAR cur_day =

    NOW ()

VAR first_wes =

    CALCULATE (

        SELECTEDVALUE ( 'SQL Results (3)'[DATE_] ),

        FILTER (

            ALL ( 'SQL Results (3)'[DATE_] ),

            YEAR ( 'SQL Results (3)'[DATE_] ) = YEAR ( cur_day )

                && MONTH ( 'SQL Results (3)'[DATE_] ) = MONTH ( cur_day )

                && DAY ( [DATE_] ) >= 1

                && DAY ( [DATE_] ) <= 7

                && WEEKDAY ( [DATE_], 3 ) = 2

        )

    )

RETURN

    CONVERT (

        IF (

            'SQL Results (3)'[DATE_] = first_wes,

            "Default Day",

            'SQL Results (3)'[DATE_]

        ),

        STRING

    )

The result looks like this:

Here is the sample.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Show your expected result with an example.

[lbendlin]

Default Date = 
var a = YEAR('SQLresults (3)'[DATE_])*100+MONTH('SQLresults (3)'[DATE_])
var m = CALCULATE(min('SQLresults (3)'[DATE_]),'SQLresults (3)',YEAR('SQLresults (3)'[DATE_])*100+MONTH('SQLresults (3)'[DATE_])=a , WEEKDAY('SQLresults (3)'[DATE_],2)=3)
return if('SQLresults (3)'[DATE_]=m,"Default Day",format('SQLresults (3)'[DATE_],"MM/dd/YYYY"))

[rschaudhr]

Thank you for providing this dax calculation. Unfortunately, it shows all the first Wednesday of every month. I was looking for first Wednesday for the current month.