Forum Discussion

fyrworx's avatar
fyrworx
Frequent Visitor
9 years ago
Solved

DATEINPERIOD issues

I am trying to create a rolling 6 week view of a count. 

 

It should be simple - an aggregate of weekly totals for the last 6 weeks of daily data. Put into a line graph.

 

I am following this guide: 

http://www.powerpivotpro.com/2013/07/moving-averages-sums-etc/

 

But running into an error when I try select the number of intervals as below:

 

 

 

 

 

 

Any ideas as to why? 

 

I realise it says day in my example. I was testing it on a day to day moving visual before creating the 6 week. I also see that PowerBI does not have a week option, any help in creating that would be appreciated too.

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi fyrworx,

     

    Please modify your DAX formula as:

    3 Month Moving Sum Units Sold =
CALCULATE (
    SUM ( 'Moving Average'[Compliant] ),
    ALL ( 'Moving Average' ),
    DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -3, DAY )
)

    Thanks,
    Yuliana Gu

2 Replies

  • fyrworx's avatar
    fyrworx
    Frequent Visitor
    9 years ago

    I seem to have figured out the error but can't get the visual to work the way I want it.

     

    Apparently the syntax requires a space after the number of intervals, before the ",".

     

    Which doesn't make sense. Anyone else have this issue?

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      Hi fyrworx,

       

      Please modify your DAX formula as:

      3 Month Moving Sum Units Sold =
CALCULATE (
    SUM ( 'Moving Average'[Compliant] ),
    ALL ( 'Moving Average' ),
    DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -3, DAY )
)

      Thanks,
      Yuliana Gu