cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JD0963 Regular Visitor
Regular Visitor

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


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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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

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]
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

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

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]
    )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.