Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
unde2667
New Member

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?

unde2667_0-1641665454629.png

 

1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

I'd probably do something like:

Current Primary = 
    CALCULATE (
        SELECTEDVALUE( Assignment[Primary Individual] ),
        Assignment[Date Start] <= TODAY(),
        Assignment[Date End] >= TODAY()
    )


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

6 REPLIES 6
unde2667
New Member

Thats it! Thanks @bcdobbs !!

bcdobbs
Community Champion
Community Champion

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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?

Hi,

Share some data, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bcdobbs
Community Champion
Community Champion

I'd probably do something like:

Current Primary = 
    CALCULATE (
        SELECTEDVALUE( Assignment[Primary Individual] ),
        Assignment[Date Start] <= TODAY(),
        Assignment[Date End] >= TODAY()
    )


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Community Champion
Community Champion

That creates a measure which would display todays primary. If that's all you need then just drop it in a card visual.

 

If you have more complicate needs downstream of that then give some details.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors