Need help on custom table/matrix format

[Sunkari]

Hi All,

We need to create following using Power BI Table or Matrix. Rows contain different metrics and columns are different time periods. Could you please provide inputs in getting follow structure.

Period	Three Months Back	Two Months Back	Previous Month	Current Month	YTD
Metric
Metrc1	100	150	200	120	570
Metrc2	200	100	250	220	770
Metrc3	300	200	300	300	1100

[v-shex-msft · Accepted solution]

Hi @Sunkari,

I'd like to suggest you create four measures to calculate these result, then drag them to matrix with Period as Rows field, date as Columns field.

Perv Month = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-1,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&FORMAT([Date],"yyyy mmm")=FORMAT(calculateDate,"yyyy mmm")),[Amount])

Perv 2 Month = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-2,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date].[MonthNo]=MONTH(calculateDate)&&[Date].[Year]=YEAR(calculateDate)),[Amount])

Perv 3 Months = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),MAX([Date].[MonthNo])-3,MAX([Date].[Day]))
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date].[MonthNo]=MONTH(calculateDate)&&[Date].[Year]=YEAR(calculateDate)),[Amount])

YTD = 
var temp=LASTNONBLANK(Smaple[Type],[Type])
var calculateDate=DATE(MAX([Date].[Year]),1,1)
Return
SUMX(FILTER(ALL(Smaple),[Type]=temp&&[Date]>=calculateDate&&[Date]<=MAX(Smaple[Date])),[Amount])

BTW, current table/matrix not support auto summary the previous value.

Regards,

Xiaoxin Sheng

[Sunkari]

Thanks bro.I will try this and let you know.

[Sunkari]

@Phil_Seamark: Any inputs

[Sunkari]

@v-ljerr-msft:Any inputs