Sales previous Year to date

[adieball]

Hi

I've gone through literally hundreds of posts to find an answer for this, tried a myriad of suggestions, all end up in the same problem.

I have two measurements:

TotalSalesYTD

=CALCULATE(SUM(Invoice[NetPriceEuro]), DATESYTD(DateKey[Date]))

TotalSalesPreviousYTD

=CALCULATE(SUM(Invoice[NetPriceEuro]), SAMEPERIODLASTYEAR('DateKey'[Date]))

My Problem is that the TotalSalesPreviousYTD always shows the Total of the complete previous year and not the data from 01.01 of last year to todays date.

I have the same problem in PowerBI and PowerPivot

My DateKey table is continious, i.e. 01.01.13 - 31.12.2020

any help is highly appreciated

Thanks

[v-ljerr-msft]

Hi @adieball,

The formula below should also work. 

TotalSalesPreviousYTD =
CALCULATE (
    TOTALYTD ( SUM ( Invoice[NetPriceEuro] ), 'DateKey'[Date] ),
    SAMEPERIODLASTYEAR ( 'DateKey'[Date] )
)

Regards

[adieball]

Hi

nope, this also show the sum of the complate last year, ot the last year up to todays date.

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, show the expected result there.

[adieball]

Hi

sorry, can't share the file as it contains sensitive data.

What I need:

Let's assume we did 100.000k€ in the complete last year (2016)

Today is the 31.10.17

I want a measure that shows me how much sales we have done last year (01.1.16 - 31.10.16)

I then need the same for previous year quester, etc. but the more "simple" SAMEPERIODLASTYEAR isn't working ...

[v-ljerr-msft · Accepted solution]

Hi @adieball,

---

What I need:

Let's assume we did 100.000k€ in the complete last year (2016)

Today is the 31.10.17

I want a measure that shows me how much sales we have done last year (01.1.16 - 31.10.16)

I then need the same for previous year quester, etc. but the more "simple" SAMEPERIODLASTYEAR isn't working ...

---

Could you try the formula( without any time intelligence functions) below to see if it works in your scenario.  

TotalSalesPreviousYTD =
CALCULATE (
    SUM ( Invoice[NetPriceEuro] ),
    FILTER (
        ALL ( Datekey ),
        'DateKey'[Date]
            >= DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
            && 'DateKey'[Date] <= EDATE ( TODAY (), -12 )
    )
)

Regards

[Ashish_Mathur]

Hi,

As long as months are dragged from the calendar table, my formula should work.  Dummy your dataset and share the download link.

[adieball]

When having the months in the rows and the years in the column I see the data for the months and the TOTALYTD summary, everyhting looks good.

As soon as I drag the PreviousYTD (or any measurement using SAMEPERIODLASTYEAR) into thevalues, I get the error:

"Funktion 'SAMEPERIODLASTYEAR' only works with contiguous date selections" ......

[Ashish_Mathur]

Hi,

I am assuming that you have months in the visual as well.  What happens when you try this

=CALCULATE([TotalSalesYTD],SAMEPERIODLASTYEAR('DateKey'[Date]))

Does this work?

[adieball]

I have months as well (as quarter, weeks, etc).

Your formular also shows the complete sum of last year, not the sum of last year up to todays date (in last year)

[MFelix]

Hi @adieball,

Try this measure:

TotalSalesPreviousYTD =
TOTALYTD (
    SUM ( Invoice[NetPriceEuro] ),
    DATEADD ( 'DateKey'[Date], -1, YEAR )
)

Regards,

MFelix

[adieball]

I get the following error when trying to do so

[MFelix]

Sorry for asking this but it appears to me in the print that between the -1 and year its not a comma.

[adieball]

You're right, on the screenshot it looks like a dot but it's actually a comma