Calculated Column - Show Last Week's Data in Current Week

[GSPBI]

Dear Microsoft PowerBI community,

I have been a long time user of this forum and I really appreciate all the users that raised their queries and the those that respond to them.

I have come across an issue that seems easy but I can't figure it out.

I have a table that looks like the below.

A	B	C	D
Id	Publication Number	Actual	Planned
ExvfxB9PdcoRCuLKJalw	37	1
ExvfxB9PdcoRCuLKJalw	36		1
fbHK1m9nAueKizAdKsWC	37	1
fbHK1m9nAueKizAdKsWC	36		1
FuTeNOWeSSQ2tp4zhQ8V	37	1
FuTeNOWeSSQ2tp4zhQ8V	36		1
G10bnm9UMTzoUtBeoyTl	37	1
G10bnm9UMTzoUtBeoyTl	36		1

I want to create a calculated column F that moves last publication's planned figures (column D) in the current publication.

A	B	C	D	E	F
Id	Publication Number	Actual	Planned	No. Activities Achieved	No. Activities Planned
ExvfxB9PdcoRCuLKJalw	37	1		1	1
ExvfxB9PdcoRCuLKJalw	36		1
fbHK1m9nAueKizAdKsWC	37	1		1	1
fbHK1m9nAueKizAdKsWC	36		1
FuTeNOWeSSQ2tp4zhQ8V	37	1		1	1
FuTeNOWeSSQ2tp4zhQ8V	36		1
G10bnm9UMTzoUtBeoyTl	37	1		1	1
G10bnm9UMTzoUtBeoyTl	36		1

What is the DAX calculated column that I need to produce? 

Any guidance would be appreciated.

Kind regards,

GSPBI

[GSPBI]

Thank you very much @tamerj1 and @Russell-PBI for your responses. I appreciate your help! 👨‍🏫

[v-eqin-msft]

Hi @GSPBI ,

If the Publication Number is continuous and  there is only one row for each Id and each Publication Number, please try:

No. Activities Planned 1 = CALCULATE(MAX('Table'[Planned]),FILTER('Table',[Id]=EARLIER('Table'[Id]) && [Publication Number]=EARLIER('Table'[Publication Number])-1))

Or

No. Activities Planned 2 = LOOKUPVALUE('Table'[Planned],'Table'[Id],[Id],[Publication Number],[Publication Number]-1) 

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @GSPBI 

many options. 
simplist is to to use LOOKUPVALUE and search for the same publication code but for publication number - 1

[Russell-PBI · Accepted solution]

Hi @GSPBI, this seems to work:

No. Activities Planned = 
VAR PublicationNumber = 'Table'[Publication Number]
VAR PreviousPublicationNumber = 
CALCULATE(
Max('Table'[Publication Number])
 , ALLEXCEPT('Table','Table'[Id])
 , ('Table'[Publication Number] < PublicationNumber)
 )
RETURN
CALCULATE(
MAX('Table'[Planned])
, ALLEXCEPT('Table', 'Table'[Id])
, 'Table'[Publication Number] = PreviousPublicationNumber
)