Forum Discussion

nerra's avatar
nerra
Helper II
7 years ago
Solved

Filtering on Last complete week

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 we...
Need Help
  • nerra's avatar
    nerra
    7 years ago

    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

BobBI's avatar
BobBI
Resolver III
7 years ago

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's avatar
GKALOK
New Member
4 years ago

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 🙂