topic Re: measure to get Previous year values in DAX Commands and Tips

measure to get Previous year values

RobertoD — Tue, 17 Oct 2023 10:40:13 GMT

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!

Re: measure to get Previous year values

Anonymous — Tue, 17 Oct 2023 12:25:44 GMT

Hi,

I think this one might work for you:

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

Re: measure to get Previous year values

RobertoD — Tue, 17 Oct 2023 12:31:31 GMT

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.

Re: measure to get Previous year values

Anonymous — Tue, 17 Oct 2023 12:34:41 GMT

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?

Re: measure to get Previous year values

RobertoD — Tue, 17 Oct 2023 12:40:49 GMT

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). 🙂

Re: measure to get Previous year values

Anonymous — Tue, 17 Oct 2023 12:54:04 GMT

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

Re: measure to get Previous year values

RobertoD — Tue, 17 Oct 2023 13:22:55 GMT

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! 😉