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

Hi pkilo

what formula you are using now?

  • pkilo's avatar
    pkilo
    Frequent Visitor
    9 years ago

    I use this one:

    Sales 7 days ago = CALCULATE(SUM('Table'[Sales]);FILTER(('Table');(DATEADD('Table'[Date]; -7; DAY))) )

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

      try this (didn't checked):

      Sales 7 days ago =
CALCULATE (
    SUM ( 'Table'[Sales] );
    FILTER (
        ALL ( 'Table'[Date]; 'Table'[Sales] );
        DATEADD ( 'Table'[Date]; -7; DAY )
    )
)
      • 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.