March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
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
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
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!
I use this one:
Sales 7 days ago = CALCULATE(SUM('Table'[Sales]);FILTER(('Table');(DATEADD('Table'[Date]; -7; DAY))) )
try this (didn't checked):
Sales 7 days ago = CALCULATE ( SUM ( 'Table'[Sales] ); FILTER ( ALL ( 'Table'[Date]; 'Table'[Sales] ); DATEADD ( 'Table'[Date]; -7; DAY ) ) )
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.
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 ) )
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
86 | |
77 | |
49 |
User | Count |
---|---|
166 | |
149 | |
99 | |
73 | |
57 |