Only show last 24 hours from 6am?

[GilesWalker]

Hi everyone,

A little stumped with this one. I need to create a filter so that the data in the report on shows the last 24 hours from 6am each morning. Basically once the clock ticks over 6am I need the report to only show me everything that has happened over the 24 hours prior.

The tables containing the data do have timestamps but are random, none will be at 6am. I have created a DateKey table using dax formula which has all dates.

Hope some one has an answer to this.

Thanks,

Giles

[v-shex-msft · Accepted solution]

Hi @GilesWalker,

You can use below formula to get min date, then use filter function to get the result data.

Measure:

minTime = 
var temp= NOW()
return
if(HOUR(temp)>=6,temp,DATE(YEAR(temp),MONTH(temp),DAY(temp)-1)+TIME(6,MINUTE(temp),SECOND(temp)))

Table formula:

New Table= FILTER(ALL(TABLE),[Date]>=[minTime]&&[Date]<=Now())

Result sample:

1.

Now:  1/6/2017 15:00

Date Range: 1/6/2017 6:00 ~ 1/6/2017 15:00

2.

Now:  1/6/2017 5:00

Date Range: 1/5/2017 6:00 ~ 1/6/2017 5:00

Regards,

Xiaoxin Sheng

[jorgeaguirre]

I just need the last 3 hours, you could adapt the formula to do that. help!!!

[Anonymous]

THis should explain it from start to finish.

TABLE = the table you sum from

COL = the colum you sum from

DATE = your date colum. 

3 hrs=0,125 day. 

European:

LAST6HRS = CALCULATE( COUNT(TABLE[COL]) ; TABLE[DATE] > NOW()-0,125  )     

US

 LAST6HRS = CALCULATE( COUNT(TABLE[COL]) ; TABLE[DATE] > NOW()-0.125  )