Previous period

[Tinus1905]

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: 

Date	Week	Period	Current week
1-1-2024	1	1	False
2-1-2024	1	1	False
8-1-2024	2	1	False
9-1-2024	2	1	False
15-1-2024	3	1	False
16-1-2024	3	1	False
22-1-2024	4	1	False
23-1-2024	4	1	False
29-1-2024	5	2	False
30-1-2024	5	2	False
5-2-2024	6	2	False
6-2-2024	6	2	False
12-2-2024	7	2	False
13-2-2024	7	2	False
19-2-2024	8	2	True
20-2-2024	8	2	True

This is what I want:

Date	Week	Period	Current week	Previous period
1-1-2024	1	1	False	True
2-1-2024	1	1	False	True
8-1-2024	2	1	False	True
9-1-2024	2	1	False	True
15-1-2024	3	1	False	True
16-1-2024	3	1	False	True
22-1-2024	4	1	False	True
23-1-2024	4	1	False	True
29-1-2024	5	2	False	False
30-1-2024	5	2	False	False
5-2-2024	6	2	False	False
6-2-2024	6	2	False	False
12-2-2024	7	2	False	False
13-2-2024	7	2	False	False
19-2-2024	8	2	True	False
20-2-2024	8	2	True	False

Can anyone help me with this?

[Vijay_A_Verma]

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]

@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]

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]

@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. 

[Vijay_A_Verma]

Have you paste the query properly which I had given? See below, you will exactly get the same output which you are asking. 

[Tinus1905]

@Vijay_A_Verma ah I forgot the first "period". But I have a little problem with my dataset. 

I have a table with +- 80.000 rows and with this formula it is loading and loading and loading for hours. 

Is there an other formula for this. I dont mind if its a column with true, false, 1 ,2 etc. I only want to filter so that the period is the previous period. 

[Vijay_A_Verma · Accepted solution]

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"

[Tinus1905]

@Vijay_A_Verma it works!!! Thanks for your help.