topic Re: 24 hour day switch in DAX Commands and Tips

24 hour day switch

amandabus21 — Mon, 29 Aug 2022 17:29:37 GMT

Hello,

How can I change my 24 hour revenue "day" to be from 2:59AM - 3:01AM the next day?

My data is in the current form of 12:00 - 12:00 but need a seperate column/ measure for our working revenue day.

Re: 24 hour day switch

amitchandak — Tue, 30 Aug 2022 06:41:34 GMT

amandabus21 , Assume you have timestamp (Date + time)

Create a new column like

Correct date =

var _time = timevalue([Datetime])

var _date - datevalue([Datetime])

return

if(_time < time(3,0,0) , _date -1, _date)

Change this as per need

Re: 24 hour day switch

amandabus21 — Wed, 31 Aug 2022 22:40:06 GMT

Hi,

I only have one table named "OccurenceTimeStamp" The table has data and time already together.

I want the slicer to be able to filter between 2:59AM - 3:01AM and not change my data.

Re: 24 hour day switch

Anonymous — Thu, 01 Sep 2022 06:44:13 GMT

Hi amandabus21 ,

First, you need to create a time dimension table. And do not create any relationship with the fact table. Then you can create a measure with the conditions:

if the time between 2:59 and 24:00 then current day
if the time between 0:00 and 3:01 then current day+1

How did you want to display the data on the visual using the fact table? Could you please provide more details on your requirement? Thank you.

Best Regards

Re: 24 hour day switch

amandabus21 — Thu, 01 Sep 2022 23:24:11 GMT

So do i need to create two seperate columns? One with date and one with time?

If i do that, what measure do I use after that?

I want to filter all my information based on the 2:59AM - 3:01AM clock. So for example I want to know how many late buses happened on 9/1/22. but on 9/1/22 from 2:59AM - 3:01AM

Re: 24 hour day switch

amandabus21 — Mon, 12 Sep 2022 18:36:16 GMT

Hello,

I am still unable to solve