Forum Discussion
Getting PowerBI to show current excel function that uses today() expression
My PowerBI pulls from about 8 excel files. One of the things it returns is the name of someone assigned to a certain date range. This comes from an excel sheet where names are assigned to weeks and I use the today() expression within lookup to display the name assigned to that week. While PowerBI refreshes, it is still pulling the old name based on the last time the excel sheet was saved. If I open the excel sheet and save it, PowerBi then pulls the right name. Is there a way I can get PowerBI to update the excel sheet automatically? Do I need to use PowerAutomate to open/save/close the sheet daily?
I'd probably do something like:
Current Primary = CALCULATE ( SELECTEDVALUE( Assignment[Primary Individual] ), Assignment[Date Start] <= TODAY(), Assignment[Date End] >= TODAY() )
6 Replies
I'm sure you could use power automate
to recalculate a sheet.
If I were you though I'd pull the raw data table in and write a measure to reproduce the logic inside power bi.
That makes sense to just have it look it up within PowerBI... Thanks! I'm not great with DAX, can you help with the equation? in Excel it's
=LOOKUP(2,1/($A$29:$A$34<=TODAY())/($B$29:$B$34>=TODAY()),$C$29:$C$34)Within PowerBI, would it be a "lookupvalue" function?
I'd probably do something like:
Current Primary = CALCULATE ( SELECTEDVALUE( Assignment[Primary Individual] ), Assignment[Date Start] <= TODAY(), Assignment[Date End] >= TODAY() )
