Ly calculated column

[nerra]

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

[v-juanli-msft · Accepted 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

[nerra]

Thank you Maggie! 

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