Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi ,
We are building a report in power bi .
This is a matrix, where column is YYYY-MM.
It also has 2 other columns 12M and 3M.
Here 3M = ((Current month – Prev 3 Month)/prev 4 month)*100
For example – 3M = (column (2016-07)- column (2016-04)) / column (2016-03)*100
And 12M = ((Current Month – Prev 12 Month)/prev 13 month)*100
For example – 12M = (column (2016-07)- column (2015-07)) / column (2015-06)*100
We are not able to do the above calculations, Can you please us suggest how can this be done. Thanks
Hi @RacAgr,
You can try to use below formula if them suitable for your requirement:
Sample measure:
CurrMonth= var currType=LASTNOBLANK(Table[Type],[Type]) reuturn SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(MAX(Table[Date]),"yyyy-MM")),[Amount])) 3m= var currDate=MAX(Table[Date]) var currType=LASTNOBLANK(Table[Type],[Type]) var prev3M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-3,1),"yyyy-MM")),[Amount])) var prev4M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-4,1),"yyyy-MM")),[Amount])) return (CurrMonth-prev3M)/prev4M*100 12m= var currDate=MAX(Table[Date]) var currType=LASTNOBLANK(Table[Type],[Type]) var prev12M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-12,1),"yyyy-MM")),[Amount])) var prev13M=SUMX(FILTER(ALL(Table),[Type]=currType&&FORMAT([Date],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-13,1),"yyyy-MM")),[Amount])) return (CurrMonth-prev12M)/prev13M*100
If above not help, please share some sample data to test.
Regards,
Xiaoxin Sheng
Hello @v-shex-msft
Thanks for the reply. but it does not seem to be working as expected.
attached the same file and expected result.
Hello all, can someone please help here. Thanks!
Hi @RacAgr,
I modify the formula based on your sample data, perhaps you can try it if it works on your side.
CurrMonth = var currType=LASTNONBLANK(Sheet2[Partner],[Partner]) return SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT(Sheet2[StartDat],"yyyy-MM")=FORMAT(MAX(Sheet2[StartDat]),"yyyy-MM")),[Price]) 12m = var currDate=MAX(Sheet2[StartDat]) var currType=LASTNONBLANK(Sheet2[Partner],[Partner]) var prev12M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-12,1),"yyyy-MM")),[Price]) var prev13M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-13,1),"yyyy-MM")),[Price]) return DIVIDE([CurrMonth]-prev12M,prev13M,BLANK()) 3m = var currDate=MAX(Sheet2[StartDat]) var currType=LASTNONBLANK(Sheet2[Partner],[Partner]) var prev3M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-3,1),"yyyy-MM")),[Price]) var prev4M=SUMX(FILTER(ALL(Sheet2),[Partner]=currType&&FORMAT([StartDat],"yyyy-MM")=FORMAT(Date(Year(currDate),Month(currDate)-4,1),"yyyy-MM")),[Price]) return DIVIDE([CurrMonth]-prev3M,prev4M,BLANK())
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft
I tried to used the same formula mentioned by you for 3M
please find the screen shot below (pC3m)
but the same column is getting repeated for each yyyy-mm. requirement is to have it in the last only.
i am using the matrix
with partners on rows
YYYYMM_date on columns
and Price and pC3m on values.
Hi @RacAgr,
>>but the same column is getting repeated for each yyyy-mm. requirement is to have it in the last only
Current matrix not support to custom the calculation of row/column total. If you add the measure to value field, it will auto generate at each column, I also not find a solution to hide these generated columns.
Regards,
Xiaoxin Sheng