cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nerra Helper I
Helper I

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
Community Support

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

View solution in original post

2 REPLIES 2
Community Support
Community Support

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

View solution in original post

nerra Helper I
Helper I

Re: Ly calculated column

Thank you Maggie! 

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors