Forum Discussion

Richard76's avatar
Richard76
Helper II
6 years ago
Solved

Time intelligence formulae

Hi Folks,

really struggling with some time intelligence formaulae on power BI. I'm trying to work out sales figures up to the same day the previous year. So in example below I need to compare from 1/1/19/ - 8/1/2019 . I was using

Same Period LY = Calculate( [Total Sales], PREVIOUSYEAR( 'Time'[PK_Date] )) which worked fine when I only had 2 years date ie 2018 & 2019. Now I have 3 years its not working so how do I amend the starting point of the calculation which will be 1/1/2019 ??

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Jesus it's still morning here I can't seem to do what I was thinking 😂
    There was a parentisis missing sorry!!

    Sales LY = CALCULATE([Total Sales],DATESBETWEEN('Time'[PK_Date],MIN('Time'[PK_Date]),MAX('Time'[PK_Date])-365)

    BR,

    DR

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Richard76 ,

     

    I assume all your relationships are done between the date and fact table.

     

    So I would do 

    Same Period LY = Calculate( [Total Sales], sameperiodlastyear( 'Time'[PK_Date] ))

    or

    Same Period LY = Calculate( [Total Sales], DATEADD('Time'[PK_Date] ,-1,year)

     

    Let me know if it worked, if so mark as solution.

     

    Best Regards,

    DR

    • Richard76's avatar
      Richard76
      Helper II
      6 years ago

      Sorry I'm probably not explaining clearly. I already have a formulae in as you suggested but the issue is I also have a date slicer in as well which runs from 1/1/19  - 8/1/2020 . When I try the formulaes you suggested it gives me the sales for the full year (I have data running from 2018) but I only want from 1/1/19 - 8/1/19 if that makes sense 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi again Richard76,

         

        I may have misunderstood, in that case I would use dates between in the calculate formula, like

         

        LY Sales = calculate([total sales],DATESBETWEEN(DATE,MIN(DATE),MAX(DATE)-365)

         

        I'm not sure but I think this might work, let me know 👌