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
AndyRothmann
Frequent Visitor

Generic Last Year Measure

Hi Together,

 

i often have the requirement to show the previous year value of a measure. Currently i create a additional "Sales Amount PY" measure for every measure, which is in the model. So i have for example 20 instead of 10 measures.

 

So is there an chance to create a dynamic measure which automatically recognizes the "original Measure", so that only need to add a single previous year measure? Or is there a workaround?

 

Thanks in advance

Andy

 

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

that depends on your data structure, and how you specify PY


if you have a slicer that selects CY/PY then you could do something like this:

Smart Sales v1 =
SWITCH (
    SELECTEDVALUE ( Slicer[CY_PY] ),
    "CY", SUM ( Sales[Value] ),
    "PY", CALCULATE ( SUM ( Sales[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ),
    BLANK ()
)

if you have a data structure like this:
MeasureValueDate

Sales 1 Tuesday, 1 January 2019
Quantity 2 Tuesday, 1 January 2019
Profit 3 Tuesday, 1 January 2019
Cost 4 Tuesday, 1 January 2019
Sales 5 Tuesday, 1 January 2019
Quantity 6 Tuesday, 1 January 2019
Sales 6 Monday, 1 January 2018
Quantity 5 Monday, 1 January 2018
Profit 4 Monday, 1 January 2018
Cost 3 Monday, 1 January 2018
Sales 2 Monday, 1 January 2018
Quantity 1 Monday, 1 January 2018

you could do something like this

SmartValue =
SWITCH (
    SELECTEDVALUE ( Slicer[CY_PY] ),
    "CY", SUM ( Table[Value] ),
    "PY", CALCULATE ( SUM ( Table[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ),
    BLANK ()
)

and then sales would be just:

Sales = CALCULATE([SmartValue], Table[Measure] = Sales)

this solution has the benefit that you can also add YTD, QTD, MTH calculation within same logic



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

3 REPLIES 3
Stachu
Community Champion
Community Champion

that depends on your data structure, and how you specify PY


if you have a slicer that selects CY/PY then you could do something like this:

Smart Sales v1 =
SWITCH (
    SELECTEDVALUE ( Slicer[CY_PY] ),
    "CY", SUM ( Sales[Value] ),
    "PY", CALCULATE ( SUM ( Sales[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ),
    BLANK ()
)

if you have a data structure like this:
MeasureValueDate

Sales 1 Tuesday, 1 January 2019
Quantity 2 Tuesday, 1 January 2019
Profit 3 Tuesday, 1 January 2019
Cost 4 Tuesday, 1 January 2019
Sales 5 Tuesday, 1 January 2019
Quantity 6 Tuesday, 1 January 2019
Sales 6 Monday, 1 January 2018
Quantity 5 Monday, 1 January 2018
Profit 4 Monday, 1 January 2018
Cost 3 Monday, 1 January 2018
Sales 2 Monday, 1 January 2018
Quantity 1 Monday, 1 January 2018

you could do something like this

SmartValue =
SWITCH (
    SELECTEDVALUE ( Slicer[CY_PY] ),
    "CY", SUM ( Table[Value] ),
    "PY", CALCULATE ( SUM ( Table[Value] ), SAMEPERIODLASTYEAR ( Calendar[Date] ) ),
    BLANK ()
)

and then sales would be just:

Sales = CALCULATE([SmartValue], Table[Measure] = Sales)

this solution has the benefit that you can also add YTD, QTD, MTH calculation within same logic



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi,

 

first i want to thank you very much for the help.

I will try it tomorrow and take a look if it works for me.

 

Greetings andy

Hi,

 

first i want to thank you very much for the help.

I will try it tomorrow and take a look if it works for me.

 

Greetings andy

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.