Forum Discussion

Sunkari's avatar
Sunkari
Icon for Responsive Resident rankResponsive Resident
9 years ago
Solved

Need help on custom table/matrix format

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 struct...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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