Forum Discussion
Variation between periods
- Anonymous2 years ago
Hi, edsonsouza
Thanks for ExcelMonke reply. You can refer to his reply. If it does not work, I use Field Parameter to create two slicers, you can try following DAX.
DAX:Changes = VAR _choose_type = SELECTEDVALUE ( 'ChooseType'[Year/Month] ) // If choose single Year VAR _currentYear = SELECTEDVALUE ( 'Date'[Year] ) VAR _currentYearSales = CALCULATE ( SUM ( Sales[sales] ), YEAR ( Sales[Date] ) = _currentYear, ALL ( Sales ) ) VAR _previousYear = _currentYear - 1 VAR _previousYearSales = CALCULATE ( SUM ( Sales[sales] ), YEAR ( Sales[Date] ) = _previousYear, ALL ( Sales ) ) // If choose Year and Month // If choose single month VAR _yearChanges = DIVIDE ( _currentYearSales - _previousYearSales, _previousYearSales ) VAR _year_Month = SELECTEDVALUE ( 'Date'[Year/Month] ) VAR _currentMonth = CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[Year/Month] = _year_Month ) VAR _currentMonthSales = CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _currentMonth ) VAR _countRows = CALCULATE ( COUNTROWS ( Sales ) ) VAR _previousMonth = IF ( NOT ISBLANK ( _countRows ) && _countRows = 1, EOMONTH ( _currentMonth, -2 ) + 1 ) VAR _previousMonthSales = CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _previousMonth ) VAR _singleMonthChanges = DIVIDE ( _currentMonthSales - _previousMonthSales, _previousMonthSales ) // If choose multiple months VAR _minMultiMonths = CALCULATE ( MIN ( Sales[Date] ), ALLSELECTED ( 'Date'[Date] ) ) VAR _maxMultiMonths = CALCULATE ( MAX ( Sales[Date] ), ALLSELECTED ( 'Date'[Date] ) ) VAR _minSalesWhenMultiMonths = CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _minMultiMonths ) VAR _maxSalesWhenMultiMonths = CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _maxMultiMonths ) VAR _salesChangeMultiMonths = DIVIDE ( _maxSalesWhenMultiMonths - _minSalesWhenMultiMonths, _minSalesWhenMultiMonths ) VAR _result = IF ( _choose_type = "Year" && ISFILTERED ( 'Date'[Year] ), _yearChanges, IF ( _choose_type = "Year/Month" && ISFILTERED ( 'Date'[Year/Month] ) && _countRows = 1, _singleMonthChanges, IF ( _choose_type = "Year/Month" && ISFILTERED ( 'Date'[Year/Month] ), _salesChangeMultiMonths, "Please choose all slicers" ) ) ) RETURN _resultBest Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, edsonsouza
Thanks for ExcelMonke reply. You can refer to his reply. If it does not work, I use Field Parameter to create two slicers, you can try following DAX.
DAX:
Changes =
VAR _choose_type =
SELECTEDVALUE ( 'ChooseType'[Year/Month] )
// If choose single Year
VAR _currentYear =
SELECTEDVALUE ( 'Date'[Year] )
VAR _currentYearSales =
CALCULATE (
SUM ( Sales[sales] ),
YEAR ( Sales[Date] ) = _currentYear,
ALL ( Sales )
)
VAR _previousYear = _currentYear - 1
VAR _previousYearSales =
CALCULATE (
SUM ( Sales[sales] ),
YEAR ( Sales[Date] ) = _previousYear,
ALL ( Sales )
)
// If choose Year and Month
// If choose single month
VAR _yearChanges =
DIVIDE ( _currentYearSales - _previousYearSales, _previousYearSales )
VAR _year_Month =
SELECTEDVALUE ( 'Date'[Year/Month] )
VAR _currentMonth =
CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[Year/Month] = _year_Month )
VAR _currentMonthSales =
CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _currentMonth )
VAR _countRows =
CALCULATE ( COUNTROWS ( Sales ) )
VAR _previousMonth =
IF (
NOT ISBLANK ( _countRows )
&& _countRows = 1,
EOMONTH ( _currentMonth, -2 ) + 1
)
VAR _previousMonthSales =
CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _previousMonth )
VAR _singleMonthChanges =
DIVIDE ( _currentMonthSales - _previousMonthSales, _previousMonthSales )
// If choose multiple months
VAR _minMultiMonths =
CALCULATE ( MIN ( Sales[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR _maxMultiMonths =
CALCULATE ( MAX ( Sales[Date] ), ALLSELECTED ( 'Date'[Date] ) )
VAR _minSalesWhenMultiMonths =
CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _minMultiMonths )
VAR _maxSalesWhenMultiMonths =
CALCULATE ( SUM ( Sales[sales] ), Sales[Date] = _maxMultiMonths )
VAR _salesChangeMultiMonths =
DIVIDE (
_maxSalesWhenMultiMonths - _minSalesWhenMultiMonths,
_minSalesWhenMultiMonths
)
VAR _result =
IF (
_choose_type = "Year"
&& ISFILTERED ( 'Date'[Year] ),
_yearChanges,
IF (
_choose_type = "Year/Month"
&& ISFILTERED ( 'Date'[Year/Month] )
&& _countRows = 1,
_singleMonthChanges,
IF (
_choose_type = "Year/Month"
&& ISFILTERED ( 'Date'[Year/Month] ),
_salesChangeMultiMonths,
"Please choose all slicers"
)
)
)
RETURN
_result
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum