Variation between periods

[edsonsouza]

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: variation of (largest year/month available) x (immediately previous year/month largest available year/month). For example: the month of March is selected, the variation will be between March/2024 and February/2024.

2) If there are 2 or more months selected: variation between the largest and smallest year/month available. For example: I selected January/2024, February/2024 and March/2024, the variation will be March/2024 x January/2024.

3) If a year filter was selected and there was no month selection: variation in the value of the highest year x the value of the immediately previous year. For example: the year 2024 is selected, the variation will be between 2024 x 2023.

Is it possible to create this measure?

And I want to display the value in text showing the % of variation and also with a decreasing arrow whether it is an increase (up arrow) or fall (down arrow) or a "delta" triangle ∆.

Attached images as an example of what I want to show:

[Anonymous · Accepted solution]

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

[ExcelMonke]

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.