cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nerra Regular Visitor
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 ;
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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Ly calculated column

Hi @nerra

Assume your data is like below

6.png

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
    )
)

7.png

 

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
    )
)

5.png

Best Regards

Maggie

2 REPLIES 2
Community Support Team
Community Support Team

Re: Ly calculated column

Hi @nerra

Assume your data is like below

6.png

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
    )
)

7.png

 

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
    )
)

5.png

Best Regards

Maggie

nerra Regular Visitor
Regular Visitor

Re: Ly calculated column

Thank you Maggie! 

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