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

  • mohan_g_das1986's avatar
    mohan_g_das1986
    Helper III
    7 years ago

    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?

     

     

  • GKALOK's avatar
    GKALOK
    New Member
    3 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 🙂