Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RobertoD
Regular Visitor

measure to get Previous year values

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!

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

View solution in original post

6 REPLIES 6
LarryFisherman
Frequent Visitor

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

LarryFisherman_0-1697547213457.png

 

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
Frequent Visitor

Hi,

I think this one might work for you:

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

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.

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?

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors