Ly calculated column

Question

does anybody know how to derive a last year sales column based on the current year?&nbsp;ly sales should correspond to the same column but different date. basically, viewing the filters (columns ) for ty net sales and then calculating ly.&nbsp;&nbsp;the formula bellow is not correct.&nbsp;Column = IF(VALUE(RELATED('filter)) =1&amp;&amp; VALUE(RELATED(filter)) =1 &amp;&amp;VALUE(RELATED('filter)) =1 &amp;&amp;VALUE(RELATED(filter)=1 ;CALCULATE(sum(Sales[Net Sales]);dateadd(Sales[BusinessDate];-364;DAY);0))&nbsp;Example&nbsp;Day&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Store&nbsp; &nbsp; &nbsp;Sales&nbsp; &nbsp;Sales LY2018-08-28&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp;92017-08-29&nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9&nbsp; &nbsp; &nbsp; &nbsp; ...&nbsp; &nbsp;&nbsp;&nbsp;Thanks,Nera

v-juanli-msft · Accepted Answer

Hi&nbsp;nerra
Assume your data is like below

Create calculated columns
year = YEAR([day])this&nbsp;year&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Sheet3[sales]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Sheet3&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[year]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;[year]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[store]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;[store]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))last&nbsp;year&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Sheet3[sales]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Sheet3&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[store]&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;[store]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[year]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;EARLIER&nbsp;(&nbsp;[year]&nbsp;)&nbsp;-&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;))

&nbsp;
Or two measures
[Measure] is total sales of this year, [Measure&nbsp;2] is total sales of last year.&nbsp;
Measure =
CALCULATE (
    SUM ( Sheet3[sales] ),
    FILTER ( ALL ( Sheet3 ), [year] = MAX ( [year] ) &amp;&amp; [store] = MAX ( [store] ) )
)

Measure 2 =
CALCULATE (
    SUM ( Sheet3[sales] ),
    FILTER (
        ALL ( Sheet3 ),
        [store] = MAX ( [store] )
            &amp;&amp; [year]
                = MAX ( [year] ) - 1
    )
)

Best Regards
Maggie

Forum Discussion

Ly calculated column

2 Replies

Related content

LY Fiscal Calculation

LY MTD Calculation

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

Calculate LY & LY-1 value for the selected YYYYMM in slicers