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
Microsoft Employee
8 years ago

Hi Harshal,

 

Please try out this measure. It works in my test Direct Query to SQL Server.

LastCustomWeek =
VAR lastWed =
    CALCULATE (
        MAX ( DateDimension[Date] ),
        FILTER (
            ALL ( 'DateDimension' ),
            'DateDimension'[Date] <= TODAY ()
                && 'DateDimension'[Weekday] = 4
        )
    )
RETURN
    IF (
        MIN ( 'DateDimension'[Date] ) <= lastWed
            && MIN ( 'DateDimension'[Date] )
                >= lastWed - 6,
        1,
        BLANK ()
    )

DAX_flag_for_Last_Completed_Week1

 

Best Regards,

Dale

  • harshaltannu's avatar
    harshaltannu
    Regular Visitor
    8 years ago

    Thank you both for your replies, appreciate it. 

     

    Dale, the measure you created works like a charm. I had to adjust the criteria to make the switch day as Thursday (instead of Wednesday, as you had). Below is the updated measure. Is there a way that this calculation can be done in a Calculated Column instead? I need to add this new calculation as a page level filter. So, when the users log into the report, they see the week selected automatically, but they also have an option of changing the dates if need be. 

     

    LastCustomWeek =
    VAR lastWed =
    CALCULATE (
    MAX ( 'Date'[DATE_] ),
    FILTER (
    ALL ( 'Date' ),
    'Date'[DATE_] <= TODAY ()
    && 'Date'[Date Day] = 5
    )
    )
    RETURN
    IF (
    MIN ( 'Date'[DATE_] ) < lastWed
    && MIN ( 'Date'[DATE_] )
    >= lastWed - 7,
    1,
    BLANK ()
    )

     

    Thanks,

    Harshal