Forum Discussion

marcod's avatar
marcod
Frequent Visitor
5 years ago

Help DAX New Measure Calculation Previous Year

Hi,   I've created a measure (MEASURE_X_PREVIOUS_YEAR ) on my tabular model to compare the amount of the MEASURE_X with the same measure in the previous year working with weeks and days of the week...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

Create a new column in the Date table using:

YearWeek = Date[Year] *100 + Date[WeekNumber]

Then create the equivalent measure to:

PY week sales =
VAR _Date =
    MAX ( 'Calendar Table'[Date] )
VAR WeekNum =
    WEEKNUM ( _Date )
VAR PYWeek =
    ( MAX ( 'Calendar Table'[Year] ) - 1 ) * 100 + WeekNum
RETURN
    CALCULATE (
        [MEASURE_Y],
        FILTER ( ALL ( 'Calendar Table' ), 'Calendar Table'[YearWeek] = PYWeek )
    )
marcod's avatar
marcod
Frequent Visitor
4 years ago

Hi Paul, 

Thanks for the answer. I've tried to implement your solution, but it works great only for week aggregation and not for day values,
Example :

Day Week               MisureY          MisureY_PY

 

 

The values of days of the week  are the same of the aggragation 

 

Marco



  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Try changing the measure to:

    PY week sales =
VAR _Date =
    MAX ( 'Calendar Table'[Date] )
VAR WeekNum =
    WEEKNUM ( _Date )
VAR PYWeek =
    ( MAX ( 'Calendar Table'[Year] ) - 1 ) * 100 + WeekNum
RETURN
    CALCULATE (
        [MEASURE_Y],
        FILTER ( ALL ( 'Calendar Table' [YearWeek] ), 'Calendar Table'[YearWeek] = PYWeek )
    )