Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Total problem with SAMEPERIODLASTYEAR

I have been struggling for some time now with this small&easy(?) problem..  ☹️  Measure: Diff sales between years = IF (     ISBLANK([Total sales last year]);     BLANK();     [Total sales (SU...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Anonymous

    Not very clear .

    Maybe what you need is a formula like this：

     

    2Diff sales between years = SUMX(DISTINCT('Date'[Date]),
IF (
    ISBLANK([Total sales last year]),
    BLANK(),
    [Total sales] - [Total sales last year]
))

    Here is a demo.

    Pbix attached

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-easonf-msft's avatar
v-easonf-msft
Community Support
6 years ago

Hi, Anonymous 

Maybe you only need to change your formula as below(need to build relationships  between tables first):

Total sales last year =
SUMX (
    'Table',
    CALCULATE ( [Total sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)

It will show as below:

 

Here is a demo.

pbix attached 

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks , but this is still not right. 
Without filter the column Diff sales between years is :
1515+15970+2320 = 20280 ... it should be 19805

But it works if I apply the year filter = 2020. 

20280 = total sales (2019+2020) - Total sales last year.

But it should only show diff between years.

I guess it is not possible to do it like this.

 

Thanks , i did not link the calendar to the data bable!

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi , Anonymous

    Not very clear .

    Maybe what you need is a formula like this：

     

    2Diff sales between years = SUMX(DISTINCT('Date'[Date]),
IF (
    ISBLANK([Total sales last year]),
    BLANK(),
    [Total sales] - [Total sales last year]
))

    Here is a demo.

    Pbix attached

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks!

      This is working!

      But I will need some time to really understand how it works. 🤗

      (I have only been working with PBI for few months.)