DAX. Previous year Amount

Question

I have a single table in Powerpivot.

My columns are Account, Amount and Date. I want to calculate PrevYearAmount, but I can't fin the correct formula.

Sample data:

Account      Amount     Date            PrevYearAmount

1              100      01/01/2016      90

1              120      02/01/2016      200

2              130      01/01/2016      108

2              103      01/01/2015 

2              105      01/01/2015 

1              90       01/01/2015 

1              200      02/01/2015

tried

=CALCULATE(SUM(Hoja1[Amount]);FILTER(Hoja1;DATEADD(Hoja1[Date];-1;YEAR));FILTER(Hoja1;Hoja1[Account]))

But this returns 350 for all rows.

Also tried:

=CALCULATE(SUM(Hoja1[Importe]);DATESYTD(SAMEPERIODLASTYEAR(Hoja1[Fecha])))

but returns blank

I recommend you create a **Date** table to take advantage of Time Intelligence functions. Check [this](https://support.office.com/en-us/article/Understand-and-create-date-tables-in-Power-Pivot-in-Excel-1c8b072e-9108-442f-8115-c72ba796d31e) — alejandro zuleta, Sep 22 '16 at 17:38

score 0 · Answer 1 · answered Sep 23 '16 at 11:52

this should do the trick:

calculate(sum('Table1'[Amount]);SAMEPERIODLASTYEAR('Table1'[Date]))

Hope this helps.

But, please consider to create a date table, as it is always a good idea, to use relationships, this makes the expanding/collapsing part of DAX much easier.

DAX. Previous year Amount

1 Answers1