Dax Formula to Calculate population from min date and max date

[williamy281]

Hi,

  Another newbie to Power BI.   I'm trying to calculate the difference in population between the min and max dates.

May data is set up like this:

 Facility      date                 pop_cnt

denver      3/1/2019           10,000

denver      4/1/2019           12,000

denver      5/1/2019           14,000

boulder    3/1/2019              3,0000

boulder    4/1/2019              5,0000

boulder    5/1/2019              7,0000

So, what I need to know is the difference in denver's population between march and may 2019 (14,000 - 10,000 = 4,000, then 4,000/10,000 = 4%) . Same thing for boulder.  How do I do this in DAX ?

Thanks.

[Anonymous · Accepted solution]

I'm not entirely sure what you want (a measure, a calculated column?) but this might do what you want or might be close enough so that you can tweak it:

[Your Measure] :=
var __isOneFacilityVisible =  HASONEFILTER( FactTable[Facility] )
var __currentFacility = values( FactTable[Facility] ) 
var __minDate =
    CALCULATE(
        min( FactTable[date] ),
        FactTable[Facility] = __currentFacility,
        ALLSELECTED()
    )
var __maxDate =
    CALCULATE(
        max( FactTable[date] ),
        FactTable[Facility] = __currentFacility,
        ALLSELECTED()
    )    
var __populationForMinDate =
    CALCULATE(
        VALUES( Table[pop_cnt] ),
        FactTable[Facility] = __currentFacility,
        FactTable[date] = __minDate,
        ALLSELECTED()        
    )
var __populationForMaxDate =
    CALCULATE(
        VALUES( Table[pop_cnt] ),
        FactTable[Facility] = __currentFacility,
        FactTable[date] = __maxDate,
        ALLSELECTED()        
    )
return
    if(
        __isOneFacilityVisible,
        __populationForMaxDate - __populationForMinDate 
    )

[williamy281]

I'll have to try this, but I think it's what I need.  Yes, I was trying to create a measure for this.

Thank you.