Flag to show data from 4PM yesterday to 4PM today?

[JD0963]

Hi. hopefully someone can help with this. I have an SSAS cube in which I want to add a calculated column which I will use to filter data in Power BI.

The field I am working with is called "date created" (datetime data type)

I basically want a flag that I can use to only show data from 4PM onwards - for example, if a user viewed the report at 8am today, it would show them data from 4PM yesterday up until 8am today. If the user viewed it at 5:30pm today it would show data from 4PM today up until 5:30pm today - does that make sense? It would be as if 4pm was the start of a new day.

Thank you for any help. I appreciate it.

[v-yulgu-msft · Accepted solution]

Hi @JD0963,

First, please generate the datetime range so that we can filter displayed data.

Please refer to these measures:

Now = NOW()
Start time = TODAY()+TIME(16,0,0)
Min time = IF([Now]>[Start time],[Start time],TODAY()-1+TIME(16,0,0))
Max Time = [Now]

Then, you can use measures [Min time] and [Max Time] to filter report data, similar to:

view data =
CALCULATE (
    SUM ( 'Table 2'[Sales] ),
    FILTER (
        'Table 2',
        'Table 2'[date created] >= [Min time]
            && 'Table 2'[date created] <= [Max Time]
    )
)

Best regards,

Yuliana Gu