Filtering on Last complete week

[nerra]

Hi All, 

i'm trying to filter my report based on the last completed week. Something like

if max(fiscal week desc) ="Current Week" && Week End Flag="Y" 

then max(fiscal week) else  max(fiscal week)-1

Has anybody tried something like this. My model is standard star schema. 

Thanks!

[BobBI]

Hi Nerra,

In your star schema , you must have a Date dimension. Add a new column into  your date dimension called 'IsCurrentWeek'

IsCurrentWeek = if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY()),0
                  ,if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY())-1,-1
                     ,if(WEEKNUM('Date'[Date])=WEEKNUM(TODAY())-2,-2,BLANK()
                            )
                       )
                   )

above dax will populate isCurrentweek with  different values , for example ( 0 = Current Week , -1 = previous week as so on)

now you can then restrict your report on this field ( either Visual level filter or Page level filter ) as per your requirement.

user 'Advance Filtering'

Show items when the value "is" -1

this will restrict your report to show last completed week data. you can tweak the logic to fit in your scenerio.

Hope this helps.

Good luck

Bob

[GKALOK]

Thanks you this works well. Just an add on in case if you have many years in time table.

Year and week needs to be concatenate. 

ISOYearWeek = F_M_Calendar[ISOYear] & " " & FORMAT( F_M_Calendar[ISOWeekNumber], "00")

IsCurrentWeek = if( F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21), "00"),0

,if(F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21)-1, "00"),-1

,if(F_M_Calendar[ISOYearWeek]=YEAR(TODAY() + 26 -  F_M_Calendar[ISOWeekNumber]) & " " & FORMAT( WEEKNUM(TODAY(),21)-2, "00"),-2

)

)

)

Hope this helps for someone 🙂

[mohan_g_das1986]

Hi - Your DAX is really helpful, but is there any formula filtering for all completed week. If filter -1,-2 & Blank i am not getting what i am expecting,.. what ever selection i make i see the incompete Weekno 37 (July 15 to July 21), completed week for me is Weekno36.. Can you help me?

[LivioLanzo]

Could you share a data sample? @nerra

[nerra]

Unfortunately not. it's confidential. @LivioLanzo

however, 

i have a matrix showing

week id, store, employee and a sum(hours).

i need to filter it for the last complete week. 

Data is loaded every day. WeekEnd is Wednesday. 

[LivioLanzo]

@nerra

you cannot 'fake' some of the data ? 

[nerra]

I managed to get some data but it's not quite correct since it's based on yesterday's date.

Last2Weeks =
VAR ThisWeek =
CALCULATE (
MAX( 'Fiscal Calendar'[Calendar Date] ),
FILTER (
ALL ('Fiscal Calendar' ),
'Fiscal Calendar'[Week Completed Flag]="Y"
)
)
RETURN
IF(
MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek
,1
,blank()
)

@LivioLanzo

[LivioLanzo]

@nerra

I meant more the original tables part of the model and along with expected results...

[nerra · Accepted solution]

@LivioLanzo

Unfortunately no. 

Can't share it.

Anyhow. I think I solved it. I managed to get the last 2 weeks with the metric:

Last2Weeks =
VAR ThisWeek =
CALCULATE (
MAX( 'Fiscal Calendar'[Calendar Date] ),
FILTER (
ALL ('Fiscal Calendar' ),
'Fiscal Calendar'[Week Completed Flag]="Y"
)
)
RETURN
IF(
MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek
,1
,blank()
)  

and then I just filtered the visual with the Complete Week Flag='Y' and the Last2Weeks metric to be = 1

I'm just wondering fi the second part of the return is correct.   MAX('Fiscal Calendar'[Calendar Date])+6>=ThisWeek