Different Years and Different Months Percentage Difference Comparison

[lthuraga]

Hi,

I'm looking to calculate the percentage difference between two different months of two different years using a slicer. How should I procced with this as I have no found no solution to this. 

[TomMartens · Accepted solution]

Hey @lthuraga ,

your requirement is simple but unfortunately not easy to implement. This is because there is no notion of a sequence or an ordered structure in the tabular model. For this reason, a DAX statement like the one below is more complex and relies on members of a column that can be ordered. Looking at your Month-Year members, I'm wondering about the expected result 😉

Nevertheless, the measure:

change between 2 dates = 
var selecteddates = calculatetable( VALUES( 'DimDate'[Datekey] ) , ALLSELECTED( 'DimDate' ) )
var sanityCheck = COUNTROWS( selecteddates )
return
IF( sanityCheck = 2
    , 
    var currentDate = CALCULATE( MAX( 'DimDate'[Datekey] ) )
    var DateFirst = MINX( selecteddates , [Datekey] )
    var DateSecond = MAXX( selecteddates , [Datekey] )
    var ValFirstDate = CALCULATE( [ms SalesQuantity] , 'DimDate'[Datekey] = DateFirst )
    var ValSecondDate = CALCULATE( [ms SalesQuantity] , 'DimDate'[Datekey] = DateSecond )
    var result = 
        IF( currentDate = DateSecond 
            , DIVIDE( ValSecondDate , ValFirstDate)
            , BLANK()
        )
    return result
    , BLANK()
)

A screenshot from a table visual:

Hopefully, this provides what you are looking for, at least provides you with some ideas on how you can tackle your challenge