Forum Discussion

manar_alamri's avatar
manar_alamri
Icon for Helper I rankHelper I
1 year ago

Add Previous QTD-YTD-MTD-Yesterday data

  Hello,   I am trying to add new column to compare the data with the previous data based on the filter the filter contains (QTD-YTD-MTD-Yesterday) options how I can make the prev - net sales co...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
1 year ago

Hello manar_alamri,

 

You can use the SAMEPERIODLASTYEAR, PARALLELPERIOD, or DATEADD functions to calculate previous period sales based on the selected filter.

Prev_Net_Sales = 
VAR _TimeFrame = SELECTEDVALUE('Time Frame'[Time Frame])
RETURN 
    SWITCH(
        TRUE(),
        _TimeFrame = "Yesterday", CALCULATE(SUM('Sales'[Net Sales]), DATEADD('Date'[Date], -1, DAY)),
        _TimeFrame = "MTD", CALCULATE(SUM('Sales'[Net Sales]), PARALLELPERIOD('Date'[Date], -1, MONTH)),
        _TimeFrame = "QTD", CALCULATE(SUM('Sales'[Net Sales]), DATESQTD(PREVIOUSQUARTER('Date'[Date]))),
        _TimeFrame = "YTD", CALCULATE(SUM('Sales'[Net Sales]), SAMEPERIODLASTYEAR('Date'[Date])),
        BLANK()
    )
  • manar_alamri's avatar
    manar_alamri
    Icon for Helper I rankHelper I
    1 year ago

    Thank you Sahir_Maharaj , it works. However, when selecting the filter based on the quarterly period, the data doesn't appear. Should we have completed at least three months in the current year for it to match with the previous quarter, or is there another reason?