Forum Discussion
edsonsouza
2 years agoRegular Visitor
Variation between periods
I have a sales by data table and a calendar table, I need to create the following measure: Sales variation with three conditions: 1) If there was no year filter and only or no month selected: varia...
- 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
ExcelMonke
2 years agoImpactful Individual
If your date table has your month/year, etc. in numerical value, it can be done quite simply with variables within your measure. A couple examples to consider:
YourMeasure =
VAR _SelectedMonth = CALCULATE(DateTable[MonthNumber],DateTable[Month]=SELECTEDVALUE(DateTable[Month]))
VAR _ValueSelectedMonth =
CALCULATE(FactTable[YourValue],DateTable[Month]=SELECTEDVALUE(DateTable[Month]))
VAR _ValueComparisonMonth = CALCULATE(FactTable[YourValue],DateTable[MonthNumber]=_SelectedMonth-1))
RETURN
_ValueComparisonMonth - _ValueSelectedMonth
You can use that code to apply for your years, etc.