Forum Discussion

rschaudhr's avatar
rschaudhr
Icon for Resolver II rankResolver II
5 years ago
Solved

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

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 t...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago
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's avatar
    rschaudhr
    Icon for Resolver II rankResolver II
    5 years ago

    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.