The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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?
Thanks in advance for your help
Solved! Go to Solution.
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
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
User | Count |
---|---|
161 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |