Forum Discussion

RobinW2's avatar
RobinW2
Regular Visitor
9 years ago
Solved

Comparing Daily Trend Year On Year

Hi there,   I hope you're well.  I am new to Power BI and trying to buid a graph that would compare a daily trend for Internet traffic with a month on month comparison.    I used a DAX formula wi...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi RobinW2,

     

    According to your description, I suppose you are using the following formula to generate the Lastmonth measure?

    Comparison_before := calculate(sum('Table'[comparison]), PARALLELPERIOD('Table'[Date],-1,MONTH))

    Please take a try to switch the PARALLELPERIOD function to DATEADD, then check to see if this would make it work.

    Comparison_before := calculate(

                                              sum('Table'[comparison]),

                                              DATEADD('Table'[Date],-1,MONTH)

                                                 )

    Regards

     

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
9 years ago

Hi RobinW2,

 

According to your description, I suppose you are using the following formula to generate the Lastmonth measure?

Comparison_before := calculate(sum('Table'[comparison]), PARALLELPERIOD('Table'[Date],-1,MONTH))

Please take a try to switch the PARALLELPERIOD function to DATEADD, then check to see if this would make it work.

Comparison_before := calculate(

                                          sum('Table'[comparison]),

                                          DATEADD('Table'[Date],-1,MONTH)

                                             )

Regards

 

RobinW2's avatar
RobinW2
Regular Visitor
9 years ago

Hi Michael,

 

I hope you're well. Thanks a lot for your answer, this the problem!

 

Have a good day.

 

Robin.

  • RobinW2's avatar
    RobinW2
    Regular Visitor
    9 years ago

    Hi everyone,

     

    Just quickly reopening this thread as I am facing a new issue with the formula that calculates YoY data.

     

    The formula is as follows:

     

    Sessions Prev. Year = CALCULATE(SUM('Traffic'[Sessions]), DATEADD('Traffic'[Date Right Format], -1, YEAR))

     

    However, when applying filters for half a month (e.g. 01/09 to 15/09), the previous year data calculates the whole September month rather than only for this data range. 

     

    Would anyone know what is causing the issue?

     

    Thanks.

     

    Robin.

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

      Hi RobinW2,

      Apologize for the late response. This might be related with the DATEADD function. DateAdd function only returns a single column of Datekey which overwrites existing filter on the same column.

      I would try to create some tests on this, will share the results once I finished.

      A blog for reference:

      http://mdxdax.blogspot.sg/2011/01/dax-time-intelligence-functions.html

      Regards

       

      • RobinW2's avatar
        RobinW2
        Regular Visitor
        9 years ago

        Hi Michael,

         

        I hope you're well and thanks a lot for coming back to me. I have actually fixed the answer by extending the dates after my last date on the excel file from which I pull the data. 

         

        It seems that Power BI was automatically recognising the September 2016 data as a full month (as there was no date beyond the 25/09 initially) and replicating this set up on the 2015 comparison.

         

        Thanks for your help!

         

        Robin.

    • mrsolomon's avatar
      mrsolomon
      Frequent Visitor
      9 years ago

      Thanks for posting this - this definitely worked with the DATEADD - I then showed a YoY for the month using the Area Chart and selected the month itself (but used DateKey) DateKey being from my Date Dimension table .