cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darko861
Helper II
Helper II

SELECTEDVALUE based on last quarter

Hi Community,

I have the following report:

 

darko861_0-1630054808924.png

 

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:
 
 
My date table:
darko861_1-1630055380123.png

 

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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] )
)

 
mahoneypat_0-1630165422419.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Super User
Super User

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] )
)

 
mahoneypat_0-1630165422419.png

 

Pat

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

lbendlin
Super User
Super User

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

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
))

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.