cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 weeks

a) I've added a calculated field on my "Date table" as

DATE[DT_ISODAYWEEKYEAR]=

value(DATE[DT_WEEKDAYNUM])*1000000+ value(DATE[DT_ISOWEEKNUMBERYEARINT])*10000+value(DATE[DT_ISOWEEKREFYEAR])

 DT_DATE DT_ISODAYWEEKYEAR 08/02/2014 6062014 09/02/2014 7062014 05/05/2014 1192014 06/05/2014 2192014

b) I've created an intermediate measure to calculate the value of MEASURE_X in the same week of the previous year by day

MEASURE_X_PREVIOUS_YEAR_TMP:=Calculate([MEASURE_X];Filter(ALL('DATE'); DATE[DT_ISODAYWEEKYEAR] = Max( TEMPO[DT_ISODAYWEEKYEAR])-1))

c) I've created the final Measure

MEASURE_X_PREVIOUS_YEAR :=SUMX (VALUES(DATE[DT_ISODAYWEEKYEAR]) ;[MEASURE_A_PREVIOUS_YEAR_TMP])

This Measure works great both in day calculation and in the aggregation.

 Etichette di riga MEASURE_X MEASURE_X_PREVIOUS_YEAR 2019 280 € WK 02 280 € 1 10 € 2 20 € 3 30 € 4 40 € 5 50 € 6 60 € 7 70 € 2020 497 € 280 € WK 02 497 € 280 € 1 80 € 10 € 2 81 € 20 € 3 82 € 30 € 4 83 € 40 € 5 84 € 50 € 6 1 € 60 € 7 86 € 70 €

Now I'm trying to create another MEASURE_Y_PREVIOUS_YEAR to calculate the number of the distincount of product sold in the previous YEAR (always working with weeks).

The MEASURE_Y is defined as :

MEASURE_Y:=DISTINCTCOUNT('TABLE_Y'[PRODUCT_ID])

I cannot use the same approach I've used with the MEASURE_X because it works for the single day, but in aggragation it gives the SUM of the distinctcount of the day.

I need calculate the distinctcount of PRODUCT_ID  for the TABLE_Y filtered by DATE[DT_ISODAYWEEKYEAR] -1

like in this example

 Etichette di riga MEASURE_Y MEASURE_Y _PREVIOUS_YEAR 2019 3 WK 02 3 1 1 (A) 2 2 (A,B) 3 1 (B) 4 1 (A) 5 3 (A,B,C) 6 1 (B) 7 1 (C) 2020 3 WK 02 3 1 1 (A) 2 2 (A,B) 3 1 (B) 4 1 (A) 5 3 (A,B,C) 6 1 (B) 7 1 (C)

Sorry for my English and sorry for the long post,
I hope I made myself clear.

Someone can help me ?

Best,

Marco

3 REPLIES 3
Super User

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 )
)
``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

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

Super User

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 )
)``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors