Forum Discussion

edsonsouza's avatar
edsonsouza
Regular Visitor
2 years ago
Solved

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: 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:

Need Help
  • Anonymous's avatar
    Anonymous
    2 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
    _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

2 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 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
    _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