cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

Andy

1 ACCEPTED SOLUTION
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

Thank you for the kudos 🙂

3 REPLIES 3
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

Thank you for the kudos 🙂

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.