Help DAX New Measure Calculation Previous Year

[marcod]

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

[PaulDBrown]

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]

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]

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