Forum Discussion
Anonymous
6 years agoNot applicable
Percent Difference in Matrix
Hi All, I have sum of all records in matrix and would like to get Percent Difference in Matrix. Provided the sample output below Thanks in Advance
amitchandak
6 years agoSuper User
For complete month , year you can use end of month or end of year. Last year same week is 364 days behind . So just have week no or week start date in you time table and use 364 days behind measure for last year , 7 days for last week
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
last Year Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))
last Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))Anonymous
6 years agoNot applicable
Hi amitchandak ,
Everything is working fine except weeknum. i have dec 2019 like 2019-48, 2019-49, 2020-50,2020-51,2020-52.
Here 48,49,50,51,52 denotes weeknum of 2019 year. i would like to coompare 52 with 51, 51 with 50 and so on.
if i put the below formula it is not showing correct output
last Week Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))Dowe have something like weeknum to date comparison?
Thanks in advance