Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalkhudary
Helper III
Helper III

Overall % Change to a selected Date

 

Hello, 

 

I am looking to get the % change to a selected date in a line chart. I am have wrote this dax function but it is not retreiving anything.Not sure what I went wrong here to get the difference between the selected location price index to subtract it from the original min price index on that visual. Then probably divide that by the miniumn price index as well to get the %.

 

Overall % Change to a selected Date =

 

Calculate (AVERAGE(Dim_New_Home_Price_Index[Price Index Value],selectedvalue(Calendar[Date])) - Calculate (average (Dim_New_Home_Price_Index[Price Index Value ]), min(Calendar[Date]))))

5 REPLIES 5
lbendlin
Super User
Super User

Don't use SELECTEDVALUE inside CALCULATE.  Use variables.

 

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Hello Bendlin, 

Here is a sample of the data. 

DateProvinceCityNew Housing & Residential TypePrice Index Value 
2017-01AlbertaEdmontonHouse only100
2017-01AlbertaEdmontonLand only100
2017-01AlbertaEdmontonCondo Only100.2
2017-04AlbertaEdmontonHouse only100.2
2017-04AlbertaEdmontonLand only99.4
2017-04AlbertaEdmontonCondo Only100.4
2017-07AlbertaEdmontonHouse only99.8
2017-07AlbertaEdmontonLand only99.9
2017-07AlbertaEdmontonCondo Only100.1
2017-10AlbertaEdmontonHouse only99.7
2017-10AlbertaEdmontonLand only100
2017-10AlbertaEdmontonCondo Only99.3
2018-01AlbertaEdmontonHouse only99.7
2018-01AlbertaEdmontonLand only100.1
2018-01AlbertaEdmontonCondo Only98.6
2018-04AlbertaEdmontonHouse only99.3
2018-04AlbertaEdmontonLand only100.2
2018-04AlbertaEdmontonCondo Only97.9
2018-07AlbertaEdmontonHouse only99.5
2018-07AlbertaEdmontonLand only100.5
2018-07AlbertaEdmontonCondo Only97.6
2018-10AlbertaEdmontonHouse only99
2018-10AlbertaEdmontonLand only100.5
2018-10AlbertaEdmontonCondo Only94.7

 

Annotation 2022-12-03 142749.pngAnnotation 2022-12-03 142858.png

 

When I click on one part of the line, it will show me in the card above, the %change from 2017 to the date clicked on in the visual.

 

Hope I was able to explain it well. Would really appreciate your help on this.

lbendlin_0-1670109698735.png

see attached

Hello Bendlin, 

 

I have tried this equation but did not yield the subraction that we need. I was looking what is the problem but seems it is not reading the min date value when we click on the visual.

 

When I cick on a particular point on the line visual, It is giving me the date, I clicked on and not 2017 the min date. In this condition, giving me the subraction as "0" and not for example 120 -100 = 20 as a numerator. Not sure what we need to adjust in the min date dax. The rest of equation is correct.

 

What do you think?

 

That's an interesting issue. When you click on the datapoint you are now setting the filter context so that the min and max range is the same as the clicked value. That means you need to provide your desired min value from a different, disconnected table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors