Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak ,

 

It seems like last year sales is caculating that way.

 

I have Jan month data alone in 2020. So when we put DATESYTD function so it is calculating the Jan 2019 data alone and skipping all the other 11 months of data in 2019 since because in 2020 it only have Jan month data.

 

Also do we have any option for weekly data? same as QTD,YTD and MTD same as above?

 

Thanks in Advance

amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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