PUG 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

    Posted 6 days ago
    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 6 days ago
    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 6 days ago
    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

    Posted 5 days ago

    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 5 days ago
    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 5 days ago

    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
    ------------------------------