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
williamy281
Frequent Visitor

Dax Formula to Calculate population from min date and max date

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 
    )

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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 
    )

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.

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