Calculate sales between two time values

[Dirk_97]

Hi,

I'm trying to calculate the total sales per weekday, while some of the sales happen after 00:00:00. So, what I want is to manipulate the weekdays. Example: On tuesday 03:12:49 there is a sale, but I want to put that sale at the monday before.

The 'new days' I want to create begin at 10:00:00 AM and end at 09:59:59 AM the next weekday.

Then e.g. create 7 DAX formulas:

'Sales monday' =  
'Sales tuesday' =
etc.

Can you please help me?

[Mikelytics · Accepted solution]

Hi @Dirk_97 ,

I think I have an idea. I would recommend you to manipulate the data in Power Query like the following.

Lets assume I have a list of DateTime values.

transform the data into decimal

data are translated into decimals:

Now you create a custom column. You want to shift by 10 Hours and a full 1 is like 1 day in date format. So I want to reduce the decimal value by 10/24 pieces.

Now it looks like this

After adding the custom column I transform the data back to date time

result: in the new column all DateTime values are reduced by 10 hours. Now use the new column for all your calculations.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

@ me in replies or I'll lose your thread.