Forum Discussion
Calculated Column - Show Last Week's Data in Current Week
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
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
)
4 Replies
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
)Thank you very much tamerj1 and Russell-PBI for your responses. I appreciate your help! 👨🏫
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.
