Power BI Exchange

Expand all | Collapse all

Added a "Last refreshed date" in a report

  • 1.  Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 03-20-2018 03:50 PM
    Edited by David Yee 03-20-2018 04:25 PM
    Hi everyone,

    I wanted to know if any of you have some ideas on how to add a "last refreshed" button to a report.  While I'm add it, I'd also like to be able to filter to the last 3 years minus this week.  Currently, I have a relative filter to just show the last three years but it isn't letting me add another filter on top of it.  Any ideas?

    ------------------------------
    David Yee
    Foothill Packing
    Salinas CA
    ------------------------------


  • 2.  RE: Added a "Last refreshed date" in a report

    Posted 03-21-2018 04:28 AM
    ​hello David

    I don't know if I get your problem but if you need to show when your report was last updated, please find below an idea.

    - Create a table with one column with the menu Enter data
    For instance fill in with the value "1"

    - Edit the table by replacing the M script by

    let
        Source = Table.FromRows({{DateTime.LocalNow()}},{"Update day"})
    in
        Source

    Now you have a table with the last refreshed date and you can drag and drop this field in your report.
    The value will change each time you click on refresh data

    ------------------------------
    Valentin
    ------------------------------



  • 3.  RE: Added a "Last refreshed date" in a report

    Posted 03-21-2018 04:49 AM
    Hi

    For your first question I've created a SQL request :

    Select getdate()
    from Date (table for data)

    This request is updated each time you refresh the dataset.

    The other question I would solve it by creating a new column which looks at todays date, and defines the row to be side /outside this week. And you can use this as a filter in your report

    Or you can create a measure to do the same.

    You can also set the "flag" from the SQL query if needed.

    I haven't tried this, but this is what I would try.

    Best Regards
    Terje

    ------------------------------
    Terje Theiss
    Service Analysis Manager, Coach & Mediator
    Tranbjerg J
    51314605
    ------------------------------



  • 4.  RE: Added a "Last refreshed date" in a report

    Posted 03-21-2018 07:20 AM
    Edited by Tom Blodget 03-21-2018 07:22 AM
    I do see the point of not showing the rough edge of recent data that can be caused by latencies, time of data refresh, pending corrections, etc. Complex date filtering can be accomplished with a "Date Dimension Table" (which you can create in SQL, Power Query, DAX, …). You simply add columns that are important to you and filter on them:  Fiscal Years Ago < 3 and Weeks Ago >= 1. It should be in the language of business users that can conceptualize the ideas. It's a design choice as to whether to have negatives values on Ago/Until columns or use null. I think negatives make the columns more useful but then you might need filtering like Fiscal Years Ago >= 0 and Fiscal Years Ago < 3 and Weeks Ago >= 1.

    As for the refresh date, Power BI transfers no knowledge of the user's timezone. Local time is wherever it is. SQL Server is the same. One solution is to present UTC but that requires users to apply their own timezone in their head. We used that for a while but then took advantage of our system's available "site time" function (which is implemented with SQL-CLR). We now show a table of the refresh times in the timezones of our various sites. That presents the time locally as well as acknowledging each part of the company.


  • 5.  RE: Added a "Last refreshed date" in a report

    Posted 03-21-2018 08:00 AM
    1. Create a new column in table:

    Last Refresh = NOW()

     CIEZfaKSQGWELWEbZdFy_1.jpg

     

    1. Create a new measure:

    Last Refresh Date = MAX(tablename[Last Refresh])

     dCOUkVgQTdGjTQPs9WEi_2.jpg

     

    1. In report add a card visual to display "Last Refresh Date" field

     aU5CML5hRPaCrV2NvOxy_3.jpg




    ------------------------------
    Garth Woods
    VP Information Technology
    Marine Ingredients
    Mount Bethel PA
    5702606908
    ------------------------------



  • 6.  RE: Added a "Last refreshed date" in a report

    Posted 03-21-2018 09:01 AM
    I'd like to add on to some of these other solutions.I prefer to use the measure option as it's the most straightforward and easiest to understand.

    But I'd recommend the below measure instead:
    Last Refresh = UTCNOW() - 6

    This is because once you publish your report to the Power BI Service, it is going to use the local time of wherever that server is hosted, so the NOW() function will appear incorrect. UTC time however is standard no matter where it's being calculated. In the example above, I subtract 6 because I'm based in Chicago.

    ------------------------------
    Josh Galus
    IT Support Specialist
    Power Construction, LLC.
    8475075393
    ------------------------------



  • 7.  RE: Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 05-15-2018 06:51 AM
    Hi @Josh Galus,

    I did the UTCNOW function and it is working.

    I am having problems adjusting the time zone.

    I tried to do the +/- 6 like you did, but instead of adding hours to the calculations, it is adding days to my date.

    So is there anything that you can suggest that might work.

    I am trying to add 5 hours 30 mins to the UTC time.

    Thank  you,​

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 8.  RE: Added a "Last refreshed date" in a report

    Posted 05-15-2018 08:09 AM
    it's 5:30 hours difference of 24 hours, so the formula will be:
    LastRefreshColumn = UTCNOW() + 5.5/24

    ------------------------------
    Pawel Jaszczynski
    excellab.at

    ------------------------------



  • 9.  RE: Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 03-21-2018 11:04 AM
    I usually do it through Power Query. The good thing about this is whenever you refresh the dataset, it updated with it.



    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    Xerva
    Karachi
    3452523688
    ------------------------------



  • 10.  RE: Added a "Last refreshed date" in a report

    Posted 05-15-2018 08:26 AM
    If you do the Power Query DateTime.LocalNow(), if you publish the report the timezone changes to the time zone of the server hosting it, which isn't consistent and isn't necessarily in your time zone.

    The benefit of UTCNOW() is that you can control the time zone regardless of where the hosting server is located after publishing.

    ------------------------------
    Josh Galus
    IT Support Specialist
    Power Construction, LLC.
    8475075393
    ------------------------------



  • 11.  RE: Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 05-16-2018 08:36 AM

    Okay so here is my problem with this approach.

    I tried to use the UTCNOW() in my report. It works fine but has a very huge flaw.

    Since it is UTCNOW() it takes the current time and displays that, which means every time you hit refresh on your browser, the time on the report changes.

    However, the report hasn't really refreshed, it is the time that is getting refreshed.

    If anyone can come up with a better solution, please do let me know.


    Thank you,



    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 12.  RE: Added a "Last refreshed date" in a report

    Posted 05-16-2018 08:53 AM
    I used this exactly the way it is described and it works beautifully. Thank you!

    ------------------------------
    Claudine Bruyns
    Business Systems Administrator
    Franklin TN
    ------------------------------



  • 13.  RE: Added a "Last refreshed date" in a report

    Posted 05-16-2018 08:17 PM

    This works for me:

    - Open the Query Editor
    - Add a new Blank Query
    - Select "Advanced Editor" from the top navigation ribbon
    - Remove the current code and enter the following:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.FromRows({{DateTime.LocalNow()}},{"Last Refresh"})
    in
    #"Changed Type"



    ------------------------------
    Best,

    Brendan Bannon
    Business Intelligence Analyst
    NYC Department of Education
    New York NY
    9089024678
    ------------------------------



  • 14.  RE: Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 05-23-2018 03:01 AM

    Hi @Brendan Bannon,

    Is there some way that we can use the UTC time instead of the local time.

    As mentioned above, Local time can change once it is published to the service.

    Please let me know.

    Thank you,​​



    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 15.  RE: Added a "Last refreshed date" in a report

    Posted 05-31-2018 03:40 PM

    Hello @Vishesh Jain​,

    You can get UTC by changing the code/expression to display time in that format.  Same code basically with a slightly different function.

    New code to paste into Query Editor --> Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Last Refresh Date" = _t]),
    #"Changed Type" = Table.FromRows({{DateTimeZone.FixedUtcNow()}},{"Last Refresh (UTC)"})
    in
    #"Changed Type"


    That should do the trick for ya!

    Here's a Microsoft reference with other DateTimeZone functions using M language in Power Query aka Query Editor.  If my response doesn't meet the requirements, you should be able to use the reference to slightly adjust and get your desired output.  

    DateTimeZone functions | Microsoft Docs


     

    Microsoft remove preview
    DateTimeZone functions | Microsoft Docs
    This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use.
    View this on Microsoft >

     



    ------------------------------
    Best,

    Brendan Bannon
    Business Intelligence Analyst
    NYC Department of Education
    New York NY
    9089024678
    ------------------------------



  • 16.  RE: Added a "Last refreshed date" in a report

    Bronze Contributor
    Posted 06-01-2018 04:12 AM
    Hi @Brendan Bannon,

    ​​I tried your solution and it is working, at least for now.

    If I run into any issues with the code or anything else, I will let you know.

    Thank you for taking the time to respond.

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 17.  RE: Added a "Last refreshed date" in a report

    Posted 06-04-2018 04:59 AM
    Hi David,

    Maybe you should create a source view in your data, that also will be refreshed in PBI.

    Example view (SQL):

     SQL refreshdate source

    From now on, you have a table with one single date-time value. If the PBI refresh fails, this field will show the last date-time the data succesfully refreshed.

    Greetings,
    Har

    ------------------------------
    Harry Arends
    Volkshuisvesting Arnhem
    Arnhem
    0263712712
    ------------------------------