Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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 )
)
Proud to be a Super User!
Paul on Linkedin.
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 )
)
Proud to be a Super User!
Paul on Linkedin.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
66 | |
55 |