topic Re: Calculated Column - Show Last Week's Data in Current Week in DAX Commands and Tips

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

GSPBI — Fri, 18 Feb 2022 23:53:08 GMT

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

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

Russell-PBI — Sat, 19 Feb 2022 01:34:04 GMT

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
)

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

tamerj1 — Sat, 19 Feb 2022 13:42:29 GMT

Hi GSPBI

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

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

Anonymous — Wed, 23 Feb 2022 02:08:01 GMT

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))

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.

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

GSPBI — Tue, 01 Mar 2022 15:49:39 GMT

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