how to calculate ternd over last year with monthly average measure

[Didik62]

Hi.

I need help to create the logic to get value of trend over last year.

I have create monthly average (current year) and monthly average (last year) and able to get trend % , but once i slice on area ID or Product ID, some error of visual happen.

my expectation is to know in area ID FD1 what productID that decrease/increase (also in reverse way)

Here my data

Month ID	Area ID	Product ID	Sales
202301	FD1	A	34
202302	FD1	A	12
202303	FD1	A	44
202304	FD1	A	76
202305	FD1	A	123
202306	FD1	A	32
202307	FD1	A	87
202308	FD1	A	39
202309	FD1	A	33
202310	FD1	A	12
202311	FD1	A	55
202312	FD1	A	88
202401	FD1	A	23
202402	FD1	A	23
202403	FD1	A	45
202404	FD1	A	87
202405	FD1	A	12
202406	FD1	A	17
202407	FD1	A	34
202408	FD1	A	98
202409	FD1	A	62
202410	FD1	A	60
202411	FD1	A	22
202412	FD1	A	67
202301	FD1	B	11
202303	FD1	B	34
202304	FD1	B	12
202305	FD1	B	75
202306	FD1	B	87
202308	FD1	B	1
202309	FD1	B	23
202310	FD1	B	65
202311	FD1	B	55
202401	FD1	B	64
202402	FD1	B	12
202403	FD1	B	73
202404	FD1	B	19
202406	FD1	B	98
202407	FD1	B	12
202408	FD1	B	56
202409	FD1	B	32
202411	FD1	B	44
202412	FD1	B	18
202302	FD1	C	5
202307	FD1	C	43
202312	FD1	C	43
202405	FD1	C	23
202410	FD1	C	77
202404	FD1	C	19
202406	FD1	C	98
202407	FD1	C	12
202408	FD1	C	56
202301	FD2	A	12
202302	FD2	A	17
202303	FD2	A	34
202304	FD2	A	98
202305	FD2	A	62
202306	FD2	A	60
202307	FD2	A	22
202308	FD2	A	67
202309	FD2	A	33
202310	FD2	A	12
202311	FD2	A	55
202312	FD2	A	88
202401	FD2	A	23
202402	FD2	A	23
202403	FD2	A	45
202404	FD2	A	87
202405	FD2	A	12
202406	FD2	A	17
202407	FD2	A	34
202408	FD2	A	98
202409	FD2	A	62
202410	FD2	A	60
202411	FD2	A	22
202412	FD2	A	67
202301	FD2	B	11
202303	FD2	B	34
202304	FD2	B	12
202305	FD2	B	75
202306	FD2	B	87
202308	FD2	B	1
202309	FD2	B	23
202310	FD2	B	65
202311	FD2	B	55
202401	FD2	B	64
202402	FD2	B	34
202403	FD2	B	12
202404	FD2	B	12
202406	FD2	B	55
202407	FD2	B	88
202408	FD2	B	23
202409	FD2	B	23
202411	FD2	B	45
202412	FD2	B	87
202302	FD2	C	12
202307	FD2	C	17
202312	FD2	C	34
202405	FD2	C	98
202410	FD2	C	23
202404	FD2	C	19
202406	FD2	C	98
202407	FD2	C	12
202408	FD2	C	56

and these are my expectation result

Expected Visual
Dinamically within visual 1,2 and 3
Visual 1	Year	Monthly Avg	Trend over last Year
a	2023	172.9
b	2024	213.5	23.5%	(b-a)/a
Visual 2	Area ID	Trend over last Year
	FD1	16.0%
	FD2	31.7%
Visual 3	Product ID	Trend over last Year
	A	-7.9%
	B	8.0%
	C	91.9%

Hopefully i can get your suggested solution

Thank you

[ryan_mayu]

what's the calculation logic of visual 2 and visual 3?

[Didik62]

I am sorry.

since the visual 1 can be multi year (range 5 years), i need to grasp the average of trend value for visual 2 and 3

actually, i would like to create value to determine which AREA ID and Product ID that have negative or positive trend comparing monthly average in each year of data.

Thank you

[ryan_mayu]

@Didik62 

pls see the attachment below

[Didik62]

Thank you for your help to re-create it on PBIX.

after checking, i found the [measure4] calculation seems not correct when i slice it by area ID FD1 and Product ID A meanwhile the value of [measure] is correct

FD1 2023 90.75

FD1 2024 105.25

A 2023 52.92

A 2024 45.83

[Measure4] should be -13.39%

it happen also to [measure2]

my next question is if i have more Product ID column, should i create measure for each ID collumn?

Thank you once again

[Anonymous · Accepted solution]

Hi @Didik62 ,

I created a sample pbix file(see the attachment), please check if that is what you want to.

Monthly Avg = DIVIDE(SUM('Table'[Sales]),12)

Trend over last Year = 
VAR _curyear =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _preyear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( ALLSELECTED ( 'Table'[Year] ), 'Table'[Year] < _curyear )
    )
VAR _cursales = [Monthly Avg]
VAR _pysales =
    CALCULATE (
       [Monthly Avg],
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Year] = _preyear )
    )
RETURN
    DIVIDE ( _cursales - _pysales, _pysales )

AID Trend over last Year = 
VAR _maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
VAR _lastyear = _maxyear - 1
VAR _aid =
    SELECTEDVALUE ( 'Table'[Area ID] )
VAR _cursales =
    CALCULATE ( [Monthly Avg], FILTER ( 'Table', 'Table'[Year] = _maxyear ) )
VAR _pysales =
    CALCULATE (
       [Monthly Avg],
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Area ID] = _aid
                && 'Table'[Year] = _lastyear
        )
    )
RETURN
     DIVIDE ( _cursales - _pysales, _pysales )

PID Trend over last Year = 
VAR _maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLSELECTED ( 'Table'[Year] ) )
VAR _lastyear = _maxyear - 1
VAR _pid =
    SELECTEDVALUE ( 'Table'[Product ID] )
VAR _cursales =
    CALCULATE ( [Monthly Avg], FILTER ( 'Table', 'Table'[Year] = _maxyear ) )
VAR _pysales =
    CALCULATE (
        [Monthly Avg],
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Product ID] = _pid
                && 'Table'[Year] = _lastyear
        )
    )
RETURN
    DIVIDE ( _cursales - _pysales, _pysales )

Best Regards

[Didik62]

Thank you for your help. it is perfectly solve my need.

but one thing that i wonder to add is average of each measure (trend over year, AID trend and PID trend) since the data is should be cover not only last 2 year but for 5 year.

For the average, i would like to calculate avarage of trend 2024 to 2023, 2023 to 2022, 2022 to 2021.

i have tried to create measure using

CALCULATE(AVERAGEX(SUMMARIZE('Table','Table'[YEAR],'Table'[Area ID],"A",[AID trend over last year]),[A]))

but it run so slow due to the data volume is so large.

any advice how to add this calculation inside or outside the measure?

Thank you

[ryan_mayu]

@Didik62 

you can try this

Measure 4 =

var _last=sumx(FILTER(all('Table'),'Table'[Year]=max('Table'[Year])-1&&'Table'[Area ID]=max('Table'[Area ID])&&'Table'[Product ID]=max('Table'[Product ID])),'Table'[Sales])/CALCULATE(DISTINCTCOUNT('Table'[Month ID]),FILTER(all('Table'),'Table'[Product ID]=max('Table'[Product ID])&&'Table'[Area ID]=max('Table'[Area ID])&&'Table'[Year]=max('Table'[Year])-1))

return if(max('Table'[Year])=CALCULATE(min('Table'[Year]),all('Table')),blank(),([Measure]-_last)/_last)

you can only display either area +product or product. becuase they can't simply use average function to get the result.

[Didik62]

for visual 2 and 3, using the same logic with visual 1 (for value trend over last year) and sliced with Area ID (visual 2) and with Product ID (visual 3)

i would like to create table visual on the dashboard

Thank you