Forum Discussion

harshaltannu's avatar
harshaltannu
Regular Visitor
8 years ago
Solved

DAX flag for Last Completed Week (filter)

Hello DAX gurus   I'm trying to create a filter in the DimDate table (my data table), which would put the flag as 1 when the data is in last completed week. This table sits on a SQL server, which I...
General Comment
Need Help
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Harshal.

     

    There are some limitations of applying DAX functions in the DirectQuery mode. Try out the following formula as a calculated column please.

    BTW, the dates in your sample aren't continuous. I would suggest you use a complete date table instead.

     

    LastWholeWeek =
VAR currentWeek =
    WEEKNUM ( TODAY (), 1 )
VAR weekNum =
    IF ( [Weekday] IN { 5, 6, 7 }, WEEKNUM ( [Date], 1 ) + 1, WEEKNUM ( [Date] ) )
RETURN
    IF ( weekNum = currentWeek - 1, 1, BLANK () )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Harshal,

 

Did it solve your issue?

 

Best Regards,

Dale