Forum Discussion

SHDJason's avatar
SHDJason
Advocate II
10 years ago
Solved

parallelperiod vs previousmonth vs adddate

Hi All,

 

I found a solution to this already but am confused about why it is so.  

 

I need to find a simple previous month lookup.  

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),DATEADD([Date]),-1,MONTH))

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),PREVIOUSMONTH([Date]))

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),PARALLELPERIOD([Date],-1,MONTH))

 

The first 2 measures return null. The last one gives me the right answer. Why? Aren't these all the same?  

 

Thanks for your thoughts.

  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    Hi SHDJason,

     

    In DAX, PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. So please check if the dates in date column are continuous. If the dates in the current context do not form a contiguous interval, the function returns an error.

     

    Reference:
    PARALLELPERIOD Function (DAX)

     

    Regards,

14 Replies

    • SHDJason's avatar
      SHDJason
      Advocate II
      10 years ago

      Sean

       

      Yes i do have a calendar table. I tried using that and the date column in the data table. Both worked, but only for parallelperiod.  

       

      Is it possible that PowerBI caches the data and then does not always refresh? Sometimes, if I create a formula incorrectly then correct it, the new calculated values do not load. But if I refresh the dataset, they do load. I'm wondering if it is a cache issue. 

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        10 years ago

        Hi SHDJason,

         

        In DAX, PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. So please check if the dates in date column are continuous. If the dates in the current context do not form a contiguous interval, the function returns an error.

         

        Reference:
        PARALLELPERIOD Function (DAX)

         

        Regards,

  • SamiTi's avatar
    SamiTi
    Frequent Visitor
    8 years ago

    Hi 

     

    All measures above are working well.

     

    However am getting BLANK when i try to view this by stores/product/category.

     

    Measure --> PPREVMONTH = CALCULATE([Volume],PREVIOUSMONTH('dim MS Cal'[Date]))

     

    Thanks for your help or advise.

     

     

    Thanks

    Sami

      • SamiTi's avatar
        SamiTi
        Frequent Visitor
        8 years ago

        Hi, The solution is that you need to select 1 month in order for measure to return Previous month value. 

         

        Hope this helps,

        Sam