cancel
Showing results for
Did you mean:
Highlighted
nerra Regular Visitor

Ly calculated column

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 ;

Example

Day                  Store     Sales   Sales LY

2018-08-28     1            10       9

2017-08-29     1             9        ...

Thanks,

Nera

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: Ly calculated column

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

2 REPLIES 2 Community Support Team

Re: Ly calculated column

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

nerra Regular Visitor

Re: Ly calculated column

Thank you Maggie!

Indeed, it's working. I only rewrote it to use a date column -364 !