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 to periode 13. 

My table looks like this: 

 

DateWeekPeriodCurrent week
1-1-202411False
2-1-202411False
8-1-202421False
9-1-202421False
15-1-202431False
16-1-202431False
22-1-202441False
23-1-202441False
29-1-202452False
30-1-202452False
5-2-202462False
6-2-202462False
12-2-202472False
13-2-202472False
19-2-202482True
20-2-202482True

 

This is what I want:

 

DateWeekPeriodCurrent weekPrevious period
1-1-202411FalseTrue
2-1-202411FalseTrue
8-1-202421FalseTrue
9-1-202421FalseTrue
15-1-202431FalseTrue
16-1-202431FalseTrue
22-1-202441FalseTrue
23-1-202441FalseTrue
29-1-202452FalseFalse
30-1-202452FalseFalse
5-2-202462FalseFalse
6-2-202462FalseFalse
12-2-202472FalseFalse
13-2-202472FalseFalse
19-2-202482TrueFalse
20-2-202482TrueFalse

 

Can anyone help me with this?

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"

     

     

8 Replies

  • 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