Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
pkilo
Frequent Visitor

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.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

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!

hohlick
Continued Contributor
Continued Contributor

Hi @pkilo

what formula you are using now?

Maxim Zelensky
excel-inside.pro
pkilo
Frequent Visitor

I use this one:

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

hohlick
Continued Contributor
Continued Contributor

try this (didn't checked):

Sales 7 days ago =
CALCULATE (
    SUM ( 'Table'[Sales] );
    FILTER (
        ALL ( 'Table'[Date]; 'Table'[Sales] );
        DATEADD ( 'Table'[Date]; -7; DAY )
    )
)
Maxim Zelensky
excel-inside.pro
pkilo
Frequent Visitor

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
Continued Contributor
Continued Contributor

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
    )
)
Maxim Zelensky
excel-inside.pro
pkilo
Frequent Visitor

@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?

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.