DAX flag for Last Completed Week (filter)

[harshaltannu]

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 connect via Direct Query in Power BI. A week runs from Thursday to Wednesday. Last completed week would be 1 only when we go into Thursday of this week. See examples below. 

1. When Today is April 23, 2018 (to April 25, 2018) - Last Completed Week is - April 12, 2018 to April 18, 2018 
2. When Today is April 26, 2018 (to May 02, 2018) - Last Completed Week is - April 19, 2018 to April 25, 2018

In my date table, I was able to devise CurrentWeek, LastWeek, LastLastWeek filters. Also, I'm able to devise Last Thursday to This Wednesday. What I'm unable to devise is the Last Completed Week that matches the above logic. 

Any help/directions you have for me would be awesome. 

Cheers,

Harshal

[v-jiascu-msft]

Hi Harshal,

Did it solve your issue?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

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]

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 ()
    )

Best Regards,

Dale

[harshaltannu]

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

[ChandeepChhabra]

Hi @harshaltannu

Can you please share a sample of your data ? I can give it a shot

[harshaltannu]

@ChandeepChhabra: how can I send you the sample data file? 

[ChandeepChhabra]

@harshaltannu Upload it on dropbox / google / onedrive and share the link

[harshaltannu]

@ChandeepChhabra: thanks, here is the link: https://www.dropbox.com/s/69nnw4rkk6x1s6o/DimDate.xlsx?dl=0

[ChandeepChhabra]

@harshaltannu : Can you also share a sample of the calculated column (output) you are expecting ?