Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sameperiodlastyear Sum issue

Hi All,

 

I have a problem with "Total" Previous Year sales. 

I have a specific time frame - from 1 to 16th of Jan. When I want to see a result on customer level I see actuals for current month, but for previous year I see total month. When I add date - I see correct reflection of values, but total is still full Jan of Previous year.

 

Please, HELP!!!

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous .

     

    Thank you very much for your reply and your desire to help.

    I think I found core issue - my calendar. In table with data - data is input on Sunday's (for example 1/5/2020,1/12/2020,1/19/2020 etc.). I made a calendar as (min,max) from data table. Now I changed (min,max) into (2019,2020). Basicly now, I don't have GAPs in my calendar books and now everything seems to be working.

     

    I think that was main reason of the issue.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Are you doing that with help from a date tbale like one of the two ways ?

     

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

    Try the first one too.

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak  and thank you for your help.

      I've tried first option and it didn't work neither. Also, I have data for the whole week on Sunday, so every data input is Sunday (i don't know if it matters). And still ,total is 537K while real sum should be 290.

       

      Any thoughts? 

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    That's wierd. Can you share some sample data and the expected result to have a clear understanding of your question? It will help me to do tests. You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

     

    Best Regards,

    Yuna

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous .

       

      Thank you very much for your reply and your desire to help.

      I think I found core issue - my calendar. In table with data - data is input on Sunday's (for example 1/5/2020,1/12/2020,1/19/2020 etc.). I made a calendar as (min,max) from data table. Now I changed (min,max) into (2019,2020). Basicly now, I don't have GAPs in my calendar books and now everything seems to be working.

       

      I think that was main reason of the issue.