Hi, I've searched and searched but I can't get my queries straight.
I have a transaction table:
| TransactionKey | DateKey | Account | Amount |
| 1 | 1 | 1234 | 133,50 |
| 2 | 367 | 5678 | 120,22 |
To this table we have a simple measure, "Sum of amount", which basically is SUM([Amount]).
The Date table looks like this:
| DateKey | Date | Year | Month |
| 1 | 2019-01-01 | 2019 | 01 |
| 2 | 2019-01-02 | 2019 | 01 |
| 367 | 2020-01-01 | 2020 | 01 |
The relationship between them two are 1:*, so a transaction basically relates to a date.
My question is how I can create a measure that looks at the previous years period, when user filters are apploied on [Year] or [Month].
I tried SAMEPERIODLASTYEAR() on the date column, which worked, but the filters then won't work when selecting for example [Year] = 2020, and [Month] = 1.
What I want is a measure somehing like this (in pseudo):
CALCULATE(
SUM([Amount]); ALL('Date'); FILTER(Date;Date[Year]-1)
)
It doesn't work - understandible - but it might give you a clue of what I am looking for?
I am using SSAS with compability level "SQL Server 2014 / SQL Server 2012 SP1 (1103)"
Best regards,
Linus
