Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
)
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |