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

Days ago from today

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!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

Hi @PowerdriveX6 

try to create a measure

Measure = 
var _curDate = MAX(EquipmentStatusDetail[DaysInState])
RETURN
TODAY() - _curDate 

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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

@amitchandak,

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.

az38
Community Champion
Community Champion

Hi @PowerdriveX6 

try to create a measure

Measure = 
var _curDate = MAX(EquipmentStatusDetail[DaysInState])
RETURN
TODAY() - _curDate 

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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)

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.