Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SAMEPERIODLASTYEAR ISSUE

SAMEPERIODLASTYEAR  Issue

I have a very simple data set, that is a sub set of a larger data set where I am trying to create a measure to determine a value for same period last year.

Data basically has a product line, product line description, actuals data and I need to set same period last year to get actuals data for the same period last year.

Data set contains a few rows of data for Jan 15, 2021 and Jan 15, 2022

I have a valid date table that seems to meet these criteria:

To work with Data Analysis Expressions (DAX) time intelligence functions, there's a prerequisite model requirement: You must have at least one date table in your model. A date table is a table that meets the following requirements:

  •             It must have a column of data type date (or date/time)—known as the date column.
  •             The date column must contain unique values.
  •             The date column must not contain BLANKs.
  •             The date column must not have any missing dates.
  •             The date column must span full years. A year isn't necessarily a calendar year (January-December).
  •             The date table must be marked as a date table.

The date table in my data set is named :  vw_FiscalStartDate.
Main table with data is vw_PL_Analysis

Vw_PL_Analysis has a column named pbixDate which contains full dates for transactions for the period.

I have a relationship between this column and the FUllDate column in vw_FiscalStartDate.

I have created a measure that returns GL Actuals in one column.  This seems to work.  I have calculated SAMEPERIODLASTYEAR             trying to use this measure as a variable and using the column identified in my data.  (See solution).

I either get one of three consistent responses. 

  1. Prior year = EMPTY
  2. Prior year = THIS SAME PERIOD DATA
  3. Prior year – it adds a row for a future date and gives this years values.

I am struggling here and cannot get this to work.

My solution is in a ZIPPED PBIX file located here:

https://tinyurl.com/589df3yj

 

Can anyone please help?

Need Help
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    You could cahnge the measure .

    PriorYRActuals2 = 
CALCULATE(SUM('vw_PL_Analysis'[ActualsBookUSD]),SAMEPERIODLASTYEAR('vw_PL_Analysis'[pbixDate]),ALL(vw_FiscalStartDate))

    The final output is shown below:

     

    Or change the visual.


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

     

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Your 3rd measure is the correct one. The reason that it is adding a row with a future date is that the measure produces a non-blank result for that date, which is the value from this year.

    To prevent future dates showing up there are a couple of options, at least. You could add another column onto your calendar table like

    Before Today = IF( 'Date'[Date] < TODAY(),1, 0)

    and then use that as a visual level filter, or use it inside the measure itself to only return a value when Before Today is 1. Personally I would choose the visual level filter.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    You could cahnge the measure .

    PriorYRActuals2 = 
CALCULATE(SUM('vw_PL_Analysis'[ActualsBookUSD]),SAMEPERIODLASTYEAR('vw_PL_Analysis'[pbixDate]),ALL(vw_FiscalStartDate))

    The final output is shown below:

     

    Or change the visual.


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