Forum Discussion

jb123's avatar
jb123
Frequent Visitor
9 years ago
Solved

Add column for Last Week to table

Hi,

 

I want to add a custom column to a table that will list the words Last Week.  I want to add this column so I can add slicer to the report so the users can filter to see just last week's data.  I haven't been able to get this working so as a workaround I created a duplicate copy of the table and in the edit query mode, I set a filter for last week.  This is not ideal as I have multiple tables and would prefer to have one table and let the user filter on the date.

 

Thanks.

 

 

 

 

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jb123

     

    Hi, add a custom Column:

     

    = Table.AddColumn(#"YourPreviousStep", "LastWeek", each if Date.IsInPreviousWeek([Date]) then "Yes" else "No")

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    jb123

     

    Hi, add a custom Column:

     

    = Table.AddColumn(#"YourPreviousStep", "LastWeek", each if Date.IsInPreviousWeek([Date]) then "Yes" else "No")

    • rahulv's avatar
      rahulv
      Regular Visitor
      9 years ago

      Hi buddy,

       

      I have the same doubt but since I am pretty new to Power BI, please explain this formula a bit more.
      Like what is Table.Addcolum etc.

       

      Please help!

       

      Regards

      Rahul

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        rahulv

         

        Hi, This can do in Query Editor using Power Query Langauge.

         

        = Table.AddColumn(#"YourPreviousStep", "LastWeek", each if Date.IsInPreviousWeek([Date]) then "Yes" else "No")

         

        In this case the sentence do:

         

        Add a Column named "LastWeek"  and fill with if Date is in Previous Week Put Yes else No.

         

        Also you can create with Add Custom Column with this code:

         

         

         

  • Nayan_surya's avatar
    Nayan_surya
    Helper I
    2 years ago

    Hi  jb123 ,

     

    This is a good approach but becomes impractical when you need like combination of these kinds of filters. I had faced a similar situation previously and what we ended up doing is getting a custom visual named Date picker by Powerviz. 

    It had all the required features like

    Multiple presets,

    Default selection

    Holidays

    Invalid dates highlight and much more

    So final result, the client was happy.

     

     

     

    I think you should check them out.

    Here is a link if you want to check this visual - https://appsource.microsoft.com/en-us/product/powerbivisuals/truvizinc1674781244292.date-picker-by-p...  (I believe they offer a free version too)