How to filter to the last 168 hours (exactly 7 days)

[gk2go]

I have a datetime field which i use to filter a report with. The filter is set to show data from the last 7 days including today.

Problem is that this actually shows data for the last 6 days + whatever data is available for today.

So on Friday, Feb 7 at 5am, for example it will show data from from Saturday, Feb 1 at 00:00 to Friday, Feb at 5am.

I would like it instead to show data from Friday, Jan 31 at 5am, to Friday, Feb 7 at am, i.e. 7 days exactly.

How can i achieve this? Is there a dax query to filter data to the last 168 hours? Could i then use it to create a calculate column that return true or false, is the data is in the last 168 hours or not.

[Anonymous]

Hi @gk2go ,

Please use the below DAX formula to calculate Time Difference and then use this new column as filter  to show your desire result:

TimeDiff =
VAR Time =
    DATEDIFF ( 'table'[date], NOW (), HOUR )
RETURN
    IF ( Time >= 168, "YES", "NO" )

This should filter out the 168 Hours.

NOTE : Please make sure to update your report every hour to get your desire result every hour.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

[gk2go]

The data is not updated every hour, how to select the latest 168 hours avaialable?

[Anonymous]

The above DAX formula should filter the 168 hour.

[gk2go]

That formula depends on NOW. Rather I need to select the last 168 distinct values in the datetime field. How?

[Mariusz]

Hi @gk2go 

You can add a column like below.

Column = NOW() - 'Table'[DateTime] 

 And later use Advanced Filtering like below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[gk2go]

This is not giving the desired range.

It should be

from Friday, Jan 31 at 5am, to Friday, Feb 7 at 5am, i.e. 7 days exactly. Same hours.

[Anonymous]

Hi @gk2go ,

Minus168Hours = Now() - 7 

You could create this as a measure to calculate your start date time value and use it to filter your table.

Jan