Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |