Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Previous Year Measure completely wrong

Hi All - I am pulling my hair out with overcoming this basic requirement that other BI tools have handled so well.   The requirement is that users can interact with an embedded date slicer and cont...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Figured it out!

     

    Took me a while to download the 800MB model over my phone :o

     

    The correct DAX formula should be

     

    Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),SAMEPERIODLASTYEAR(dates[date]))

     

    The PREVIOUSYEAR calc was taking the whole previous year into account even if you were only selecting a month from the current year.  Useful for somethings, but not for you.

  • Anonymous's avatar
    Anonymous
    8 years ago

    Thank you Phil_Seamark

    Something so simple turning my hairs grey.

    Appreciate your help mate,

    Jake

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi Anonymous

 

You can have multiple relationships between your sales and date table.  Only one can be active.  Active just means default, in that you can write minimal DAX code that assumes the rules of the active relationship.  You can still analyise using the other dates, only you need to write your calculations to specify which of the relationships you'd like you use.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Phil_Seamark - please see my edit to last post 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi Anonymous

     

    Do you mean about multiple relationships? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Phil_Seamark, nope, I tried your suggestion with the date table and the problem still remains 

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi Anonymous

         

        Something odd is happening.

         

        Are you able to provide me with a stripped down version of your PBIX file?

         

        Save a copy and remove all tables other than your sales table and the date table.  Remove all columns from your sales table and upload to this post (if that is ok).  That might make it easier for me to spot what is going on.