cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Community Champion

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

Community Champion

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors