Showing data of recent 24/48 hours

[Anonymous]

Hi all,

I would like to filter my data by using the slicer tool. My goal is to show only data within the last N days (or hours).

However, if I use relative filtering and filter by last 1 day (for instance), the data are not showing today's data even if I use the  "include today" option. 

What can I do to include also the newest data?

Cheers,
Marco

[v-frfei-msft]

Hi @Anonymous,

We can create measure to work on it. I made one sample for your reference.

today = IF(MAX(Table1[date])=TODAY(),1,0)

last 1 day = IF(MAX(Table1[date])<=TODAY() && MAX(Table1[date])>= TODAY()-1,1,0)

Then we can filter the visual by the masure to achieve our goal.

For more details, please check the pbix as attached.

Regards,

Frank

[Anonymous]

Thank you, Frank.

That basically works. However, if I want to have a dynamic slider (the user should be able to change the time frame) this will not I guess?
Such as:

• last 10 Days or
• Between DD/MM/YYYY and DD/MM/YYYY

Cheers,

Marco

[v-frfei-msft]

Hi @Anonymous,

Please try to use the formulas.

last 10 day = IF(MAX(Table1[date])<=TODAY() && MAX(Table1[date])>= TODAY()-10,1,0)

Between = IF(MAX(Table1[date])<=DATE(YYYY,MM,DD) && MAX(Table1[date])>= DATE(YYYY,MM,DD),1,0)

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

I can make the newest data included by this way. Could you please share your sample data to me?

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark the answer as solution to close the case.

Regards,
Frank

[Anonymous]

@v-frfei-msft sorry for the late answer!!

Unfortunatelly that didn't work for my dataset. It still does not include the actual day. It stops at midnight before...

Here the link to a test report:
https://app.powerbi.com/view?r=eyJrIjoiY2VhYzcwZWYtZWJhZC00ZjUwLWE3OTgtMTcxZjdhYTYwOThlIiwidCI6ImIwZ...

[Anonymous]

Here the detailed request of the data:

let
    Source = Xml.Tables(Web.Contents("http://dataservices.tasman.govt.nz/environmentalgraph/11/741/4/week/4/week/47/0/600/300/data?format=xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"AltSiteName", type text}, {"Latitude", type number}, {"Longitude", type number}, {"SiteId", Int64.Type}, {"SiteName", type text}}),
    #"Expanded Tests" = Table.ExpandTableColumn(#"Changed Type", "Tests", {"Test"}, {"Tests.Test"}),
    #"Expanded Tests.Test" = Table.ExpandTableColumn(#"Expanded Tests", "Tests.Test", {"Datasource", "Measurement", "MeasurementId", "Name", "Results", "Unit"}, {"Tests.Test.Datasource", "Tests.Test.Measurement", "Tests.Test.MeasurementId", "Tests.Test.Name", "Tests.Test.Results", "Tests.Test.Unit"}),
    #"Expanded Tests.Test.Results" = Table.ExpandTableColumn(#"Expanded Tests.Test", "Tests.Test.Results", {"Result"}, {"Tests.Test.Results.Result"}),
    #"Expanded Tests.Test.Results.Result" = Table.ExpandTableColumn(#"Expanded Tests.Test.Results", "Tests.Test.Results.Result", {"DT", "NonDetect", "Status", "Value"}, {"Tests.Test.Results.Result.DT", "Tests.Test.Results.Result.NonDetect", "Tests.Test.Results.Result.Status", "Tests.Test.Results.Result.Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Tests.Test.Results.Result",{"Tests.Test.Results.Result.NonDetect", "Tests.Test.Results.Result.Status"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Tests.Test.Results.Result.DT", type datetimezone}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Tests.Test.Results.Result.Value", "Value"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tests.Test.Results.Result.DT", type datetime}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Easting", "GraphBytes", "GraphTitle", "Northing", "Tests.Test.Datasource", "Tests.Test.Measurement", "Tests.Test.MeasurementId", "Tests.Test.Name"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Tests.Test.Results.Result.DT", "Date"}})
in
    #"Renamed Columns1"

[Anonymous]

So this effects not only PowerBI Desktop but also the webbased PowerBi

[Anonymous]

@v-frfei-msft? anyone?
I conntacted the support but they aren't coming back to me. 😞

[Anonymous]

Has anyone an idea?

[Anonymous]

Thank you for your reply, Frank. You are a legend.

I'm sorry, but I'm afraid that I described my problem inarticulately. 

With your formulas, you have predefined filter options (1 day, or 10 days). However, what I want to have is the slicer tool which also includes the current day. At the moment, it does not, even I tick 'Include today' in the options. 

Do you think it's a time setting anywhere in the report properties?