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 week)-1

 

 

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

Thanks!

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

9 Replies

  • 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 🙂

    • nerra's avatar
      nerra
      Helper II
      7 years ago

      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.