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 column contain the previous period ?

 

 

Need Help

4 Replies

  • 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?

  • AndersASorensen's avatar
    AndersASorensen
    Icon for Helper I rankHelper I
    1 year ago

    Is the picture what you are trying to achieve, otherwise im not quite sure what you mean. You want a visual with net sales and compare against (QTD, YTD etc). 

    I would create measures for that, not extra columns.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi all,thanks for the quick reply, I'll add more.

    Hi manar_alamri ,

    Try this

    Measure = 
VAR _timeFrame = SELECTEDVALUE('yourtable'[Time Frame])
RETURN
SWITCH(TRUE(),
_timeFrame = "Yesterday",CALCULATE([Net Sales],'FactTable'[Date] = TODAY()-1),
_timeFrame = "MTD",CALCULATE([Net Sales],DATESMTD('FactTable'[Date])),
_timeFrame = "ATD",CALCULATE([Net Sales],DATESQTD('FactTable'[Date])),
_timeFrame = "ytd",CALCULATE([Net Sales],DATESYTD('FactTable'[Date]))
)

     

     Best Regards