cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Different Years and Different Months Percentage Difference Comparison

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.

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors