Forum Discussion

michaelstone's avatar
michaelstone
Icon for Helper I rankHelper I
7 years ago
Solved

Strange behaviour with SAMEPERIODLASTYEAR

Hi,   I'm building a Power BI report which is analysing PoS transaction data so have a fact table (Transactions) with every individual transaction including timestamp and sales amount (Transactions...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi michaelstone ,

    Sorry for my mistake.

    Please modify the measure Total Sales - Previous Year  like below.

    Total Sales - Previous Year =
SUMX (
    'Calendar',
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
)

    Here is the output.

    Best  Regards,

    Cherry

     

     

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi michaelstone ,

Based on your information, I have made a test with your formula, I cannot reproduce your issue.

If it is convenient, could you share your data sample and so that we could help further on it?

You also could have a test on my test sample which has been attached below.

Best  Regards,

Cherry

 

 

michaelstone's avatar
michaelstone
Icon for Helper I rankHelper I
7 years ago

Hi v-piga-msft ,

 

Here is a link to a file which shows the issue.

 

PBIX File

 

Note that the end date is now 17 July so you need to have this as the end date to see the issue.

 

Thanks,

 

Michael

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi michaelstone ,

    By my test and research based on your pbix, I'm afraid that you should create the table with the date column from Calendar table like below so that you could get the correct output.

    Best  Regards,

    Cherry

    • suryaburaboyina's avatar
      suryaburaboyina
      Frequent Visitor
      7 years ago

      Hi,

       

      It is working fine wheni tried, if didnot set the Date columns with same format please do that.

       

      Below is hte screenshot for same.

       

       

      mark it correct, if its useful.

       

      Thanks,

      Surya Teja

      • michaelstone's avatar
        michaelstone
        Icon for Helper I rankHelper I
        7 years ago

        Hi suryaburaboyina ,

         

        I don't understand what you mean by "did not set Date column with same format".

         

        As per my instructions, the problem only occurs when you include the last day of data in the slicer i.e. 17 July 2019.

         

        Thanks,

         

        Michael

         

         

    • michaelstone's avatar
      michaelstone
      Icon for Helper I rankHelper I
      7 years ago

      Hi v-piga-msft ,

      My production PBIX does use the Calendard[Date] column but as you can see from your example, the Total at the bottom of the Total Sales - Previous Year column is still incorrect.

      I was only using Transactions[Date] in the visualisation because it showed the underlying problem i.e. that the SAMEPERIODLASTYEAR is not working correctly and is including dates to the end of the month.

      Thanks,

      Michael

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Hi michaelstone ,

        Sorry for my mistake.

        Please modify the measure Total Sales - Previous Year  like below.

        Total Sales - Previous Year =
SUMX (
    'Calendar',
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
)

        Here is the output.

        Best  Regards,

        Cherry