Forum Discussion

GSPBI's avatar
GSPBI
Frequent Visitor
4 years ago
Solved

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 acr...
  • Russell-PBI's avatar
    Russell-PBI
    4 years ago

    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
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.