DAX for a measure that calculates percent change based on user input

[Anonymous]

Hi! 

I'm trying to create a measure for my visual that calculates the percent change for a given month from the same month last year. The user is viewing a map of counties in a US state and will initially see this card with percent change showing the percent change for the whole state, and when they click on a county (or multiple) it should update to be the percent change for that county(ies). There are two slicers which allow the user to choose what month/year they are looking at.

This is where I am at for the DAX:

1-Yr % Change, Total = DIVIDE((SUM('countydata'[value]) - CALCULATE(SUM('countydata'[value]), SAMEPERIODLASTYEAR('countydata'[ldate]))), CALCULATE(SUM('countydata'[value]), SAMEPERIODLASTYEAR('countydata'[ldate]), "NA")

This doesn't give an error exactly, but it must be always dividing by 0 since the result it yields is always NA. I have a sneaking suspicion that the issue might not be the DAX (well, not only), but that it must be because this measure is only performing these calculations for whatever rows make it through the filters on the page (so a particular month/date, and then certain counties). With that being the case, is SAMEPERIODLASTYEAR simply unable to get to any data for rows that match ldates one year prior, since the slicers have filtered those rows out? (I mean that if the slicers on the page are for ldate Jan 2013, this expression can't access any rows with ldate Jan 2012: does that sound correct? is there any way to write it such that it pulls from the entire dataset and finds those rows that are one year back? is there another way of doing this entirely??)

Some example data, if the user selects counties A and B for Jan 2013:

ldate                County         value

1/1/2013          A                  100

1/1/2013          B                  30

I want the measure to calculate the Jan 2013 sum of the value as 130, and then go back to the bigger database and find the rows for those same counties for one year previous (see below) and say "okay, so that sum was 110, so the percent change must be (130-110)/110 = 0.1818"

1/1/2012          A                  70

1/1/2012          B                  40

Thanks in advance!!

[amitchandak]

@Anonymous , You should use a date table marked as date and change measure like. First Try Blank inplace of NA

1-Yr % Change, Total = DIVIDE((SUM('countydata'[value]) - CALCULATE(SUM('countydata'[value]), SAMEPERIODLASTYEAR('Date'[Date]))), CALCULATE(SUM('countydata'[value]), SAMEPERIODLASTYEAR('Date'[Date]), blank())

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.