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