Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
does anybody know how to derive a last year sales column based on the current year?
ly sales should correspond to the same column but different date. basically, viewing the filters (columns ) for ty net sales and then calculating ly.
the formula bellow is not correct.
Column = IF(VALUE(RELATED('filter)) =1
&& VALUE(RELATED(filter)) =1 &&
VALUE(RELATED('filter)) =1 &&
VALUE(RELATED(filter)=1 ;
CALCULATE(sum(Sales[Net Sales]);dateadd(Sales[BusinessDate];-364;DAY);0))
Example
Day Store Sales Sales LY
2018-08-28 1 10 9
2017-08-29 1 9 ...
Thanks,
Nera
Solved! Go to Solution.
Hi @nerra
Assume your data is like below
Create calculated columns
year = YEAR([day])
this year =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER (
ALL ( Sheet3 ),
[year] = EARLIER ( [year] )
&& [store] = EARLIER ( [store] )
)
)
last year =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER (
ALL ( Sheet3 ),
[store] = EARLIER ( [store] )
&& [year]
= EARLIER ( [year] ) - 1
)
)
Or two measures
[Measure] is total sales of this year, [Measure 2] is total sales of last year.
Measure = CALCULATE ( SUM ( Sheet3[sales] ), FILTER ( ALL ( Sheet3 ), [year] = MAX ( [year] ) && [store] = MAX ( [store] ) ) ) Measure 2 = CALCULATE ( SUM ( Sheet3[sales] ), FILTER ( ALL ( Sheet3 ), [store] = MAX ( [store] ) && [year] = MAX ( [year] ) - 1 ) )
Best Regards
Maggie
Hi @nerra
Assume your data is like below
Create calculated columns
year = YEAR([day])
this year =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER (
ALL ( Sheet3 ),
[year] = EARLIER ( [year] )
&& [store] = EARLIER ( [store] )
)
)
last year =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER (
ALL ( Sheet3 ),
[store] = EARLIER ( [store] )
&& [year]
= EARLIER ( [year] ) - 1
)
)
Or two measures
[Measure] is total sales of this year, [Measure 2] is total sales of last year.
Measure = CALCULATE ( SUM ( Sheet3[sales] ), FILTER ( ALL ( Sheet3 ), [year] = MAX ( [year] ) && [store] = MAX ( [store] ) ) ) Measure 2 = CALCULATE ( SUM ( Sheet3[sales] ), FILTER ( ALL ( Sheet3 ), [store] = MAX ( [store] ) && [year] = MAX ( [year] ) - 1 ) )
Best Regards
Maggie
Thank you Maggie!
Indeed, it's working. I only rewrote it to use a date column -364 !