measure to get Previous year values

[RobertoD]

Hi,

trying to get a PY measure. the following dax works already, but when a filter on specific sub-periods is set (months or weeks), the output is the full (previous) year value anyway. what's wrong with this script?

these are the used tables/columns:

'Dim_Tempo'[DataISO] : Time table, column is the yyyy/mm/dd date.

'Fact_Sales'[SellIN_EUR] : Values to sum depending on the set monthly/weekly filters.

SellIn_EUR_PY = 
VAR CurrentYear = YEAR(MAX('Dim_Tempo'[DataISO]))
VAR PrevYear = CurrentYear - 1
RETURN
    CALCULATE(
        SUM('Fact_Sales'[SellIN_EUR]),
        FILTER(
            ALL('Dim_Tempo'),
            YEAR('Dim_Tempo'[DataISO]) = PrevYear &&
            'Dim_Tempo'[DataISO] <= MAX('Dim_Tempo'[DataISO])
        )
    )

thank you!

[LarryFisherman]

And the table itself is also set as a date table? 😄

[RobertoD · Accepted solution]

No it isn't (Tables are set by IT Dept People and I have norole in that). But it seems I've just solved this by myself. The Dax I used to make it work is :

SellIn_EUR_PY =

CALCULATE(

    SUM([SellIn_EUR]),

    FILTER(ALL(Dim_Tempo[Anno]), Dim_Tempo[Anno] = year(max(Dim_Tempo[DataISO]) )-1))

where Dim_Tempo[Anno] is the year column (numeric).

Thank you anyway! You've opened my mind! 😉

[LarryFisherman]

Hi,

I think this one might work for you:

SellIn_EUR_PY = CALCULATE(SUM('Fact_Sales'[SellIN_EUR]), SAMEPERIODLASTYEAR('Dim_Tempo'[DataISO]))

[RobertoD]

That's a good one,  but maybe is not fitting to the formats of my tables, because syntax is correct but I can'g get any value when applied to my dataset, tho.

Thank you for the hint.

[LarryFisherman]

Hmm, okay. It should work given you have 1:N relationship with Dim_Tempo->Fact_Sales

Did you mark your Dim_Tempo as a date table?

[RobertoD]

Yes, both relationship is 1:N and Dim_Tempo DataIso is set as a date. Relation among the tables is through a common Key field ID_Date with the same numeric format yyyymmdd.

Dim_Tempo is a table also including Year, half, quarter, month, week, day as numeric fields...
This is definitely blowing my mind (i am a rookie, tho). 🙂