Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Yesterday Refresh Date

  • 1.  Yesterday Refresh Date

    Silver Contributor
    Posted 28 days ago
    Hi All,

    l have created a last refresh date 08/20/2019 4:23:11 PM  which is fine.
    I want to create  last refresh date to show yesterdays date. Im having difficulty achieving this

    Ex.
    Last Refresh Date(Yesterday's Date)
    08/19/2019 4:23:11 PM

    Please help

    Thanks
    PC

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------


  • 2.  RE: Yesterday Refresh Date

    Top Contributor
    Posted 28 days ago
    Edited by Vishesh Jain 28 days ago
    Hi @PANTRY COUPON,

    Did you try the DATEADD() function?

    In the function you can put -1 day and this will give you the previous day.

    Hope this solves your issue.

    Thank you,​

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



  • 3.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 28 days ago
    Hi  Jain,

    This is excel data from SharePoint, I will check on that

    Thanks
    PC

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------



  • 4.  RE: Yesterday Refresh Date

    Top Contributor
    Posted 28 days ago
    I am not sure if it is possible from Power Query.
    I think you need to write Stored Procedure from SQL to handle DataRefresh dates for multiple days.

    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 5.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 28 days ago
    Hi Farhan,
    This is excel date housed in sharepoint

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------



  • 6.  RE: Yesterday Refresh Date

    Gold Contributor
    Posted 28 days ago
    With your refresh date in that format, is it a TEXT field?  If so you're going to have difficulty.

    Rather than write messy queries or DAX, bring it in as a DateTime field.  Then in Query you can create a -1 date:
    Table.AddColumn(#"Previous Step", "Date -1", each Date.AddDays([LastRefresh], -1), type datetime)

    I don't think DATEADD is going to help you but you can just add a calculated column to subtract one with DAX:
    LastRefresh -1 = [LastRefresh] - 1​

    Hope this makes some sense

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 7.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 27 days ago
    Hi Simon,

    Thanks for your input

    -PC

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------



  • 8.  RE: Yesterday Refresh Date

    Posted 27 days ago
    Edited by Adam Tall 27 days ago
    Hi, I got one solution for you.

    Install Rscript
    Install Gdata Package

    Create folder C:/test

    Then just copy all the text in advanced editor
    (3 different Blank Querys in "Edit Queries"


    let
    Source = #table(type table[LastRefresh=datetime], {{DateTimeZone.LocalNow()}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)require(gdata)#(lf)write.table(trim(dataset), file=""C:\\test\\last_refresh.csv"", sep = "";"" , row.names = FALSE, col.names = TRUE, append = TRUE)#(lf)plot(dataset)",[dataset=#"Changed Type with Locale"])
    in
    #"Run R script"



    let
    Source = #table(type table[LastRefresh=datetime], {{DateTimeZone.LocalNow()}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US")
    in
    #"Changed Type with Locale"



    let
    Source = Csv.Document(File.Contents("C:\test\last_refresh.csv"),[Delimiter=";", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LastRefresh", type datetimezone}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"LastRefresh", "Refresh History"}})
    in
    #"Renamed Columns"



    After one run you need to change in the Rscprit
    "col.names = TRUE"  to  col.names = FALSE"
    Otherwise the columns names will come with the refresh.



    Filter on the "Last Refresh Yesterday" visual



    ----------------------------------------
    Adam Tall
    BI Consultant - Navet AB


  • 9.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 27 days ago
    Hi Adam,

    Can  M be user instead of Rscript.
    Rscript always comes with problems..


    Thanks
    PC

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------



  • 10.  RE: Yesterday Refresh Date

    Posted 27 days ago
    Edited by Adam Tall 27 days ago
    I dont think M can do it.
    I will try.


  • 11.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 27 days ago
    Thanks ,please let me know

    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------



  • 12.  RE: Yesterday Refresh Date

    Posted 27 days ago
    Edited by Adam Tall 27 days ago
    Do you use gateway?
    If you do there will be a logg file somewhere on the server with refresh timestamps

    ----------------------------------------
    Adam Tall
    BI Consultant - Navet AB


  • 13.  RE: Yesterday Refresh Date

    Silver Contributor
    Posted 26 days ago

    Issue resolved.
    I use
    Last Data Date = PREVIOUSDAY('Last Refresh'[Last Refreshed].[Date])

    Thank you all for your updates

    PC


    ------------------------------
    PANTRY COUPON
    DEVELOPER
    Houston TX
    8323455543
    ------------------------------