cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## % growth over PY ignore blank

Hi

I have a formula for sales growth over PY

```% Sales Growth over PY =
DIVIDE (
SUM ( 'FactSales'[Sales] );
CALCULATE (
SUM ( 'FactSales'[Sales] );
SAMEPERIODLASTYEAR ( 'DimCalendar'[Dates] )
)
) -1```

My data only starts in April 2017.

The current formula has limitations related to that

For example if on date range slicer:

May 2018 selected  =                                                                  (-2.6%)

April and May 2018 selected =                                                   (-1.6%)

March (blank in 2017), April and May 2018 selected  =            +50,4%

How can I ignore values in the selected month if the PY month has no data?

1 ACCEPTED SOLUTION
Community Support

HI @Credo,

I'd like to suggest you add a condition to compare 'previous date' and minimum date, calculate result when minimum date is 'large or equal to' previous date.

```% Sales Growth over PY =
VAR currDate =
MAX ( 'DimCalendar'[Dates] )
VAR minDate =
FIRSTDATE ( ALLSELECTED ( 'DimCalendar'[Dates] ) )
RETURN
IF (
minDate
<= DATE ( YEAR ( currDate ) - 1; MONTH ( currDate ); DAY ( currDate ) );
DIVIDE (
SUM ( 'FactSales'[Sales] );
CALCULATE (
SUM ( 'FactSales'[Sales] );
SAMEPERIODLASTYEAR ( 'DimCalendar'[Dates] )
)
)
- 1
)```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

HI @Credo,

I'd like to suggest you add a condition to compare 'previous date' and minimum date, calculate result when minimum date is 'large or equal to' previous date.

```% Sales Growth over PY =
VAR currDate =
MAX ( 'DimCalendar'[Dates] )
VAR minDate =
FIRSTDATE ( ALLSELECTED ( 'DimCalendar'[Dates] ) )
RETURN
IF (
minDate
<= DATE ( YEAR ( currDate ) - 1; MONTH ( currDate ); DAY ( currDate ) );
DIVIDE (
SUM ( 'FactSales'[Sales] );
CALCULATE (
SUM ( 'FactSales'[Sales] );
SAMEPERIODLASTYEAR ( 'DimCalendar'[Dates] )
)
)
- 1
)```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors