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

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?