SELECTEDVALUE based on last quarter

[darko861]

Hi Community,

I have the following report:

So far I have created a measure that gives me prior year's sales amount if I select e.g the year 2013 I will get last year's amount of 2.64.

The measure is:

Prior Year Selected =

VAR SelectedYear =

SELECTEDVALUE('Calendar'[Year])-1

VAR Multiselect = "Multiple Years"

RETURN

IF(ISBLANK(SelectedYear),Multiselect,CALCULATE(

SUM(Sales[SalesAmount]),

ALL('Calendar'),

'Calendar'[Year]=SelectedYear

))

If I wanted to tweak this measure in order for me to get the previous year's quarter sales how would I do that? E.g. if I Select 2013Q3 then I would like to get the corresponding result for 2012Q3.

Here is the file:

https://www.dropbox.com/s/lcebhq9423tkp3z/Contoso%20Sales%20for%20Power%20BI%20Designer.pbix?dl=0

My date table:

[mahoneypat · Accepted solution]

You can use this SPLY measure once you make these changes to your model/file.

1. Turn off Auto Date/Time in Options

2. Convert your Calendar[DateKey] to type Date instead of DateTime

3. On the Table tools tab, Mark your Calendar table as a Date table using the DateKey field to validate.

SPLY =
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    SAMEPERIODLASTYEAR ( 'Calendar'[DateKey] )
)

Pat

[lbendlin]

Use SAMEPERIODLASTYEAR() - that will work for whatever time interval you show in your visual (year,quarter, month, even day)

[darko861]

Hi,

Yes, you could create a measure like this:

PY = CALCULATE([Total sales],SAMEPERIODLASTYEAR('Calendar'[DateKey]))

which will give you the correct answer, but is there a way to alter the below code by using SELECTEDVALUE and for example SAMEPERIODLASTYEAR in a measure?

Prior Year Selected =

VAR SelectedYear =

SELECTEDVALUE('Calendar'[Year])-1

VAR Multiselect = "Multiple Years"

RETURN

IF(ISBLANK(SelectedYear),Multiselect,CALCULATE(

SUM(Sales[SalesAmount]),

ALL('Calendar'),

'Calendar'[Year]=SelectedYear

))