Time Series: Calculate 7-Day Return and Month-to-Date Return

[wlf0032]

Hi,

I have a Table (Table1) of time series that is the cumulative returns (RC) for two stocks. I want to calculate the 7-day return and the Month-to-Date return. So the mathmatical formuals are respectively:

• Return7D    =  (1+RCday_select)/(1+RCday_select-7) -1

• ReturnMTD =  (1+RCday_select)/(1+RCLastMthEnd) -1

I have created two measures based this. but the measures seem to give wrong results. How can I modify the DAX formulas to make it work?

Table1:

Date	Stock	RC
2022-01-01	Stock1	1.1%
2022-01-01	Stock2	1%
2022-01-02	Stock1	1.5%
2022-01-02	Stock2	3%
...	...	...

Return7D =
   VAR __RC_PrvW =
        CALCULATE(
              SUM(Table1[RC]),
              'Table1'[Stock] = SELECTEDVALUE(Table1[Stock]),
              'Table1'[Date] = SELECTEDVALUE(Table1[Date])-7)
RETURN
      DIVIDE(1+SUM(Table1[RC]),1+__RC_PrvW) - 1

ReturnMTD =
     VAR  __RC_PrvEoM =
             CALCULATE(
                        SUM(Table1[RC]),
                        'Table1'[Stock] = SELECTEDVALUE(Table1[Stock]),
                        LASTDATE(PREVIOUSMONTH(Table1[Date])))
RETURN
      DIVIDE(1+SUM(Table1[RC]),1+__RC_PrvEoM) - 1

[amitchandak]

@wlf0032 , You need use date table and measure like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last month end date Sales =

var _max = month(maxx(ALLSELECTED('Date'), 'Date'[Date]) )

return

CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date') , 'Date'[Date] = eomonth(_max,-1) ))

see if above can help

[wlf0032]

Thanks for your answer! But this is not what I am looking for.

I am trying to do some simple time series calculations within my measure formula. My confusion is that, in general, how can I take the value from a different date? e.g. if I want calculate: RCcontext_date / RCcontext_date-7d, how can I refer to RCcontext_date-7d in my formula?

[v-kkf-msft]

Hi @wlf0032 ，

In my sample data, your formula was able to return the correct result, could you please share your PBIX file that does not contain sensitive data?

Best Regards,
Winniz

[wlf0032]

Thanks for checking Winniz! 

Unfortunately, I can't share any data, cuz it's sensitive and much much more complicated than the sample data I provided above.

I was confused why my formula doesn't work, now I suspect that it's because I have a Many-to-One relationship for the Date column.