Forum Discussion

pkilo's avatar
pkilo
Frequent Visitor
9 years ago
Solved

Incorrect Row Totals (previous day/week/year data)

I need a calculated column, which brings the sales from the previous week (same workday) = Sales 7 days ago Date       Column1 Column2 Column3 Sales Sales 7days ago 3.1.2017 Jacket      RRPT    ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi pkilo,


    I think you should add a condition to filter with type :

     

    Sales & days ago= 
var currType= LASTNONBLANK(Table[Column3],[Column3])
reuturn
CALCULATE(SUM('Table'[Sales]),FILTER(All('Table'),Table[Date]=MAX([Date])-7&&[Column3]=currType))

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi pkilo,


I think you should add a condition to filter with type :

 

Sales & days ago= 
var currType= LASTNONBLANK(Table[Column3],[Column3])
reuturn
CALCULATE(SUM('Table'[Sales]),FILTER(All('Table'),Table[Date]=MAX([Date])-7&&[Column3]=currType))

 

Regards,

Xiaoxin Sheng

  • pkilo's avatar
    pkilo
    Frequent Visitor
    9 years ago

    Anonymous

     

    Getting close.. But not quite.

    What if the column2 value changes also?

     

    (All column values should be match, but the date should be the same day from the previous week)

     

    But I found the solution:

    Your script was almost ok. I just changed the MAX to EARLIER and after that it did work!