Forum Discussion

tash0000's avatar
tash0000
New Member
8 years ago

Period Filter with last year comparison

I followed this excellent article to create a Period table and a Slicer for a select of date ranges such as Calendar YTD, Current MTD, Fin YTD, etc.

 

I've been trying to come up with a way to present figures for Prior Period (i.e. same number of dates in range, prior to first date) and Prior Year Period (i.e. same dates, the year prior).

 

I've been able to get the dates to display for reference but I'm unable to get the metric figures for these periods. I've tried lots of different DAX functions, a separate CompareDates table as well as extra columns in my Period table but I can't seem to get anything to display the correct figures.

 

Here is a screenshot to demonstrate (the metric I'm using is Sessions, as in website sessions from Google Analytics):

 

https://imgur.com/a/MeGbW (sorry for Imgur link, couldn't figure out how to insert an image)

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi tash0000,

     

    Could you provide some sample data? From the image, you have got correct date values, then, what is your desired output? Could you illustarte the requirement with more information?

     

    Regards,
    Yuliana Gu

    • tash0000's avatar
      tash0000
      New Member
      8 years ago

      Hi v-yulgu-msft

       

      Yes, sorry, I should have indicated that the numbers highlighted in red in the screenshot are incorrect ('previous period sessions' and 'previous year period sessions')

       

      Here are some screenshots of the formulas I've been trying to calculate the number of sessions between the dates indicated.

       

      I've tried a large range of formulas with no success (you'll notice that these two are different, although they're trying to achieve a similar outcome).

       

       

      To reiterate, I'd like to calculate the sum of sessions between the dates displayed - one for previous period and one for previous period last year.

       

      The results displayed are way too high and incorrect when compared against Google Analytics data for the given periods.

       

      Hope this makes more sense. Happy to clarify. Eager to solve this :)

       

      Cheers, Tash.

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi tash0000,

         

        So what should the correct result be?  Share a link from where i can download your file.