cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcod
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_DATEDT_ISODAYWEEKYEAR
08/02/20146062014
09/02/20147062014
05/05/20141192014
06/05/20142192014

 

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 rigaMEASURE_XMEASURE_X_PREVIOUS_YEAR
2019280 € 
WK 02280 € 
110 € 
220 € 
330 € 
440 € 
550 € 
660 € 
770 € 
2020497 €280 €
WK 02497 €280 €
180 €10 €
281 €20 €
382 €30 €
483 €40 €
584 €50 €
61 €60 €
786 €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 rigaMEASURE_YMEASURE_Y _PREVIOUS_YEAR
20193 
WK 023 
11 (A) 
22 (A,B) 
31 (B) 
41 (A) 
53 (A,B,C) 
61 (B) 
71 (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
PaulDBrown
Super User
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 )
    )




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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

 

marcod_0-1630659868266.png

 

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.