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

pkilo's avatar
pkilo
Frequent Visitor
9 years ago

It change the value, but was not what I expected.

 

Kind of weird - it brings the sales value from the same row, not the 7 days ago value. And for some rows it brings nothing.

But it wasn't  the Total anymore.

 

 

 

hohlick's avatar
hohlick
Icon for Continued Contributor rankContinued Contributor
9 years ago

sorry.

here is correct code (checked on your data from initial post):

=
CALCULATE (
    SUM ( 'Table'[Sales] );
    FILTER (
        ALL ( 'Table'[Date]; 'Table'[Sales] );
        [Date]
            = EARLIER ( [Date] ) - 7
    )
)