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
    )
)
  • pkilo's avatar
    pkilo
    Frequent Visitor
    9 years ago

    hohlick

     

    No, this did not help.

    No errors - only blank cells.

     

    Date calculation works ok (I checked by using another column to calculate date),

    but the problem must be the amount of columns which generates several duplicate Date rows = Date is not unique.

     

    Is it possible to use some kind of concatenate-function  to find matching column values from previous date?