Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago
Solved

Previous period

Hi,   In PowerBi query editor I want to have a column of the previous period.  The year periods are: Week 1-4 is period 1, week 5-8 is period 2 etc etc. If a year has 53 weeks then week 53 belongs...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    In your Advanced Editor, Delete all other existing lines aftrer #"Added Custom3". Use these 3 lines after #"Added Custom3" step. 

     

        CurrentWeek = #"Added Custom3"[Period]{List.PositionOf(#"Added Custom3"[Current week], true)} - 1,
    #"Added Custom" = Table.AddColumn(#"Added Custom3", "Previous period", each [Period] = CurrentWeek)
in
    #"Added Custom"

     

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

Use this formula in a custom column where #"Changed Type" is your previous step

[Period] = #"Changed Type"[Period]{List.PositionOf(#"Changed Type"[Current week], true)} - 1

 

  • Tinus1905's avatar
    Tinus1905
    Resolver I
    2 years ago

    Vijay_A_Verma 

    You mean putting it in the advanced editor or open custom column and then the code is: 

    = #"Changed Type"[Period]{List.PositionOf(#"Changed Type"[Current week], true)} - 1

    I don't understand it completely. 

    This is the end of my advanced editor: 

     

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Current week", each Date.IsInCurrentWeek([Date]))
    in
    #"Added Custom3"

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      2 years ago

      Click on Custom Column in Add Column menu and paste the formula which I have given. Replace #"Changed Type" with #"Added Custom3". This will become

      #"Added Custom3"[Period]{List.PositionOf(#"Added Custom3"[Current week], true)} - 1
      • Tinus1905's avatar
        Tinus1905
        Resolver I
        2 years ago

        Vijay_A_Verma ok, this what I get:

         

         

        But this is what I want, so I can use a filter on true or false in the previous period column.