SAMEPERIODLASTYEAR / YTD where available data is up to the previous month

[Anonymous]

Hi community,

Looking for help to create a measure to calculate a YTD number for a SAMEPERIODLASTYEAR.

Example dataset attached for reference, this is in use within the model.

My challenge is as follows:

• I'm writing this in March, although I only have data up to February 2020 (for operational reasons, there's always a delay in updating data).
• I want to compare YTD 2020 (in this case January + February, as I have no data yet for March) with YTD 2019 (comparing the same months, January + February).
• As such, I need my measure to recognise this limitation.
• I can easily calculate the 2020 YTD, as it only has January + February data to draw upon. For 2019, however, if I use a 'SAMEPERIODLASTYEAR' calculation the value returned includes March 2019 in the answer. So it shows 2770, rather than 2165.
• Is there a way to recognise the max year as February (or whatever month the dataset goes up to, as this will be a recurring issue) when calculating the SAMEPERIODLASTYEAR?
• As usual, I have a datekey in place to assist with time calculations. It has columns for date, month, year that we can use in this measure.

Can you help?

Thanks for your support!

[V-lianl-msft]

Hi @Anonymous ,

Please try the dax below:

Previous_YTD = CALCULATE([YTD] , DATEADD(Sheet7[date],-1,YEAR))

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

[Anonymous]

Thank you for your assistance, Liang.

[amitchandak · Accepted solution]

Try like this with date Calendar

This Year =
var _max = maxx('Sales','Sales'[Date])
var _min = date(year(_max),1,1)

return
CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))

last Year =
var _max1 = maxx('Sales','Sales'[Date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
var _min = date(year(_max),1,1)

return
CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Thank you for your assistance, amitchandak.

I accepted your response as the solution because it is allows additional flexibility to be adapted easily and logically for similar circumstances and other columns in my situation. The other responses elsewhere were also very helpful and worked too - thanks to the other users for their input.

[Mariusz]

Hi @Anonymous 

Sure, you can combine time intelligence functions like below.

Sales Last Year To Date = 
CALCULATE(
    [Sales], --your measure
    DATESYTD(
        SAMEPERIODLASTYEAR( 'Calendar'[Date] ) --date from your date dimension / calendar table
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thank you for your assistance, Mariusz.