Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
darko861
Resolver II
Resolver 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
Employee
Employee

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

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


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.