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        White 100
3.1.2017 Jacket      RRPT         Black 50
3.1.2017 Jacket      RRPT          Blue 20
...
10.1.2017 Jacket    RRPT        White         120    100
10.1.2017 Jacket    RRPT        Black          85        50
10.1.2017 Jacket    RRPT        Blue           10         20

I can do that easily, if I have only one row per date. But now, when I have more than one row per date,
it brings the total sum of sales by date.

10.1.2017 Jacket    RRPT      White         120       170 WRONG
10.1.2017 Jacket    RRPT      Black           85        170 WRONG
10.1.2017 Jacket     RRPT     Blue            10         170 WRONG

I need the total sum of exact row values per date and per columns from the previous period.

Thanks.

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

8 Replies

    • 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
        Continued 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 )
    )
)
  • 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!