Calculate average of month-on-month percentage, for all categories, same date

[JohnBI]

Hi,

A "simplified" version of my problem, which consists of 3 columns, namely CATEGORY, DATE, VALUE

Sample RAW DATA

My goal is to get : Average of month-on-month percentage for category A and B, for each row (DATE in my case)

----------------------------------------------------------------

This is what I have achieved so far. The table below is not my final goal. The final goal is the table above, as noted.

(Please note the "DATE[Date]" column below is from lookup calendar table, with relationship.)

SUM = SUM ( MYTABLE[VALUE] )

MoM% = 
VAR Last = [SUM]
VAR First = CALCULATE ( [SUM], DATEADD ( 'DATE'[Date], -1, DAY ) )

RETURN
100 * ( Last / First - 1 )

The real life problem requires me to get weighted average of data (vs simple average here), and for more than 20 different data in CATEGORY column (vs only two data here, namely A and B). I simplify the problem jz to make your life easy. Many thanks !!

[Phil_Seamark]

Try this code and make sure you are using the Date column from your Date table on visual and not the date column from MyTable.  Don't forget to format the measure as a percent and adjust the number of decimal points.

I might not have the calc right for you in the last line, but you can see it's easy enough to adjust.

Measure = 
VAR LastMonth = CALCULATE(
					SUM(MyTable[Value]),
					DATEADD(
							'Date'[Date],
							-1,
							MONTH)
					)
Var ThisMonth = CALCULATE(SUM(MyTable[Value]))					

return DIVIDE((LastMonth-ThisMonth),ThisMonth,0)		

[JohnBI]

Hi Phil,

First of all, many thanks for your quick reply !!

I think I might have explained it wrongly, hence your solution doesnt work after I alter the value of CATEGORY B to very different values, as shown below.

Actually, my problem can be solved very easily in EXCEL, as followed:

The COLUMNS HIGHLIGHTED IN RED are my final goals.

(COLUMNS DATE, A, B here are the raw data, as noted in my first post, just that I unpivot/pivot with Query editor)

In PBI, I think I could have done it by using CALCULATED COLUMN (instead of MEASURES) with EARLIER function, but I would like to use MEASURES as it is more versatile for later use.

Hope I successfully clarify my issue. Thanks in advance !!

[Phil_Seamark]

I could have broken these out to separate measures but give this a try.

Averaqe of MoM% for A & B = 

VAR ThisMonthA = CALCULATE(
			SUM('MyTable'[Value])
			,FILTER(
			ALLEXCEPT(MyTable,'Date'[Date]),[Category]="A")
				)
VAR LastMonthA = CALCULATE(
			SUM('MyTable'[Value]),
			FILTER(
			ALLEXCEPT('MyTable',MyTable[Date]),[Category]="A"
				),
			DATEADD('Date'[Date],-1,MONTH)
			)

VAR ThisMonthB = CALCULATE(
			SUM('MyTable'[Value])
			,FILTER(
			ALLEXCEPT(MyTable,'Date'[Date]),[Category]="B")
			)

VAR LastMonthB = CALCULATE(
			SUM('MyTable'[Value]),
			FILTER(
			ALLEXCEPT('MyTable',MyTable[Date]),[Category]="B"
			),
			DATEADD('Date'[Date],-1,MONTH)
			)

VAR MomA = DIVIDE((ThisMonthA-LastMonthA),LastMonthA)		
VAR MomB = DIVIDE((ThisMonthB-LastMonthB),LastMonthB)
					
RETURN DIVIDE(MomA + MomB,2)

[JohnBI · Accepted solution]

Hmm....guess that would work too but its not practical for many data in the CATEGORY column.

Anyway thanks Phil, I have worked it out alrdy

Fact table name: ONE

Date table name: DATE

SUM = 
SUM ( ONE[VALUE] )

MoM = 
VAR Last = [SUM]
VAR First = CALCULATE ( [SUM], DATEADD ( 'DATE'[Date], -1, DAY ) )

RETURN
IF ( NOT ( ISBLANK ( First ) ),
	100 * ( DIVIDE ( Last, First ) - 1 )
)

MoM_AVG = 
AVERAGEX ( 
    SUMMARIZE ( ONE, ONE[CATEGORY] ),
    [MoM]
)

Thanks for your help Phil