Measure: Calculate a value same period last year (from the start of year and today)

[nannimora]

Hi,

I would like to do a measure that calculate sales of same period last year. 

now i use this:

Venduto PY = CALCULATE(
        SUM('Vendite'[YY. Valore Tot. Documento]);SAMEPERIODLASTYEAR(Calendario[Date])
        )

but in this way it considers the sales untill the end of the month. I would like to have sales for example from 01/01/2019 to 04/07/2019 (todaY)

Thanks for the help.

Regards 

[Icey]

Hi @nannimora ,

Please let me know whether this problem has been solved.

Best Regards,

Icey

[nannimora]

Hi, 

no the problem isn't solved.

I have modified the pbix posted by @Icey 

The Calendar Table now is:

Calendar = 
CALENDAR(
    MIN('Value Table'[Date]);
    MAX('Value Table'[Date])
)

and i added two new measures:

Sales = CALCULATE(SUM('Value Table'[Value]))

Sales PY = CALCULATE([Sales];SAMEPERIODLASTYEAR('Calendar'[Date]))

Now as u can see.

the matrix shows me the weeks and the week n. 15 of the previous year sums all the value untill the end of the months. 

Es. week 15, columns "Sales PY" = 205.

Istead  of the right value that has to calculate until 08/04/2019. 

I hope you could undestand what I tried to explain. Sorry for my elementary English 🙂

Now I see that i am not able to insert a .pbix files.

https://www.dropbox.com/s/3acpeyyy661amnp/Measure-Calculate%20a%20value%20same%20period%20last%20yea... 

Thank's for the support.

[Icey · Accepted solution]

Hi @nannimora ,

Sorry to reply late. Please check:

Sales PY 2 =
VAR LastDay =
    LASTDATE ( 'Calendar'[Date] )
VAR LastYearDay =
    DATEADD ( LastDay, -1, YEAR )
RETURN
    CALCULATE (
        [Sales],
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] ),
        'Calendar'[Date] <= LastYearDay
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nannimora]

Now is perfect. Thank you so much for the support!!

[Icey]

Hi @nannimora ,

How about creating measures like so:

Sum Measure ( 2020 ) = 
CALCULATE (
    SUM ( 'Value Table'[Value] ),
    YEAR ( 'Calendar'[Date] ) = YEAR ( TODAY () )
        && 'Calendar'[Date] <= TODAY ()
)

Sum Measure ( 2019 ) =
VAR ThisYear =
    YEAR ( TODAY () )
VAR LastYear = ThisYear - 1
VAR LastYearToday =
    DATE ( LastYear, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        SUM ( 'Value Table'[Value] ),
        YEAR ( 'Calendar'[Date] ) = LastYear
            && 'Calendar'[Date] <= LastYearToday
    )

For more details, please check the attached PBIX file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AilleryO]

Hi,

SAMEPERIODLASTYEAR uses the period displayed in your table.

So if your table is "by month", SAMEPERIODLASTYEAR takes the whole month.

If you make a table by day, it should work... But maybe yuo want it by month ?

Other solution is to use a different function, maybe calculating a start date and end date, using DATEADD ?

Like here :

https://community.powerbi.com/t5/Desktop/Dax-Calculate-Min-amp-Max-of-Dates/td-p/502929

Is that of any help ?