• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot DAX PREVIOUSYEAR Function Challenge

JasonMinhas

New Member
Background
I'd like to create a measure that finds the values for the previous year on a quarter level. Using the PREVIOUSYEAR function is pretty straightforward in itself but the challenge is that it also row/column context from a field that comes from another table. I have redacted this file but have included notes in the first tab for context if it helps.

Objective
In the "Pivot Table" sheet you will see the measure "M_A_Last_Year_Qtr_Customers (Using Column)" (highlighted green). This measure uses a calculated column and shows the correct values because it takes into account the T-shirt Size field that resides in another table. The measure below it "M_A_Last_Year_Qtr_Customers (Using Measure)" is incorrect and I need it the have the same values as the other measure without using the calculated column.

Restrictions
The Pivot table format and dimension fields (Year, quarter, T-shirt Size) must stay the same and come from the current tables. For example, I can't change the T-shirt Size field to come from the same table the measure resides in because I will eventually expand this model to include other tables that will also use the T-shirt_Size_Helper table and I need the relationships to stay as is.
 

Attachments

  • Financial_Model_V17.9_Redacted.xlsx
    640.4 KB · Views: 1
Last edited:
Where's your date dimension?

Without date dimension table, you can't use time intelligence functions. When you do, you'll get unexpected results.
If you had date dimension, it would simply be...
=CALCULATE(([Target1 Customers]),SAMEPERIODLASTYEAR(DateTable[DateColumn]))

Also... you have non optimized data model for DAX analytics. I'd recommend considering restructuring your model to conform to Star Schema or Star Constellation (Galaxy) Schema pattern.
 
Where's your date dimension?

Without date dimension table, you can't use time intelligence functions. When you do, you'll get unexpected results.
If you had date dimension, it would simply be...
=CALCULATE(([Target1 Customers]),SAMEPERIODLASTYEAR(DateTable[DateColumn]))

Also... you have non optimized data model for DAX analytics. I'd recommend considering restructuring your model to conform to Star Schema or Star Constellation (Galaxy) Schema pattern.
I have a DateTime formatted column, "c_A_Year_Date_Format" specifically so I could use the PREVIOUSYEAR function. Any recommendations on how to change the current model? I'm familiar with the Star Schema structure but Power Pivot requires a 1:M relationship which is why I felt the need to create FY Quarter Helper and Year_Helper. Ideally, I would only want one table with datetime right?1697142133033.png
 
What you have is Snowflake schema. While it has its use. Should be avoided whenever possible to avoid downstream headache.

Typically, you can create single calendar dimension table combining Year Helper, FY_Quarter_Helper.
As well, you can probably pivot out Scn_Seasonality and Scn_Ramp and combine it to calendar dimension.

Though hard to give you exact answer, as I'm not familiar with your full scope.
 
Back
Top