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: varia...
  • 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