Add Previous QTD-YTD-MTD-Yesterday data

[manar_alamri]

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 ?

[v-zhouwen-msft]

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

[Sahir_Maharaj]

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]

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]

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.