Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, gurus!
Im newbie to PBi and I have a problem with date manipulations.
I have direct query to some source, that not controlled by me at all.
This source have column "Days In State", that mean that something was happen with this row equipment.
And source doesnt have column of DATE direcly, so, only age of last actions.
How to get custom column/measure/anything, that can show me date of this last actions?
In PowerQuery Editor I try Date.AddDays(DateTimeZone.UtcNow(),-[DaysInState]), it CALCULATE that i need, but require switch to import query from direct query, that is not acceptable.
I try instead of it create New Measure or New Column but its not working at all for all my approaches.
Even simple New Colum = today()-EquipmentStatusDetail[DaysInState] say me OLE DB error "We couldn't fold the expression to the data source. Please try a simplier expression". Whats more simplier?!
Google'ing is not bring me any working solution.
Is there any way to resolve this quest?
Thanks in advance!
Solved! Go to Solution.
try to create a measure
Measure =
var _curDate = MAX(EquipmentStatusDetail[DaysInState])
RETURN
TODAY() - _curDate
@PowerdriveX6 , In case of measure it need to be
New Colum = today()-max(EquipmentStatusDetail[DaysInState] )
Then use the row context get the at appropriate place
sorry, but in start you mention "measure" but on next row you suggest "new column".
If you know working way via New column, please, describe as now your suggested solution is not for New column.
try to create a measure
Measure =
var _curDate = MAX(EquipmentStatusDetail[DaysInState])
RETURN
TODAY() - _curDate
This measure work perfect!
Maybe you have some resource that i can use as dummy-guide to learn Measures? 🙂
I work a lot with Excel and VBA before, but now im willing to study some PBI, but excel experience worth almost nothing here 🙂
Upd:
Improved a little your solution:
_LastActivity =
var _curDate = MAX(EquipmentStatusDetail[DaysInState])
RETURN
if(ISBLANK(_curDate), "",TODAY() - _curDate)
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |