cancel
Showing results for
Did you mean:
Highlighted
nerra 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 ;

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

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

## 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 Helper I

## Re: Ly calculated column

Thank you Maggie!

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

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘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
Users online (730)