Units Sold Since 5pm Yesterday

[Chaucer]

Hi There,

I'm trying to generate a report that show the units sold since 5pm yesterday. 

My Data table has a Date/Time column, and a Date column. I have a fairly standard Date table with various Date's in it, but no Date/Time Values.

Any bright ideas?

Thanks

[Vvelarde · Accepted solution]

@Chaucer

Hi, Try this to obtain sales from 5PM of Previous Day

Calculated Column 

1. InvoiceDate-OnlyDate = 'All Invoices'[Invoice Date].[Date]  --Modeling - Format -- To only Date

2. InvoiceDate-Diff =
DATEDIFF('All Invoices'[InvoiceDate-OnlyDate],TODAY(),DAY) * 100 + hour('All Invoices'[Invoice Date])

In level filter add:

Regards

Victor

[Eric_Zhang]

---

@Chaucer wrote:

Hi There,

I'm trying to generate a report that show the units sold since 5pm yesterday. 

My Data table has a Date/Time column, and a Date column. I have a fairly standard Date table with various Date's in it, but no Date/Time Values.

Any bright ideas?

Thanks

---

@Chaucer

You can try to create a measure as below. Then put the Date[date] as axis field and put the measure as value field.

measure = 
VAR CURRENTDATE = MAX ( 'DATE'[Date] )
RETURN
SUMX (
    FILTER (
        ALLSELECTED ( sale ),
        (
            sale[date]
                = CURRENTDATE - 1
                && HOUR ( sale[date/time] ) >= 17
        )
            || (
                sale[date] = CURRENTDATE
                    && HOUR ( sale[date/time]) < 17 
            )
    ),
    sale[amount]
)

[Chaucer]

Hi Eric,

That's great, thanks!

Two minor problems:

My date table has dates in it until the end of 2020 (I'm guessing that that is not how to do it, but I couldn't be asked to figure out how to get my Date Table to auto update. I do however have a column in my Date Table called Today = IF(Dates[Date]=TODAY(),1,0)

Also, I've realised that I don't actually want the sales from 5pm Yesterday, I want the sales from the most recent 5pm that has occured on a workday. I.e. if it's 3pm on Thursday, I want the sales since 5pm on Wednesday. If it's 6pm on Thursday, I want the sales since 5pm on Thrusday, and if it's 2 pm on Monday, I want the sales since 5pm on Friday.

Sorry!

[Anonymous]

Hi @Eric_Zhang,

It is possible to create a dynamic date table. 

DateDIM (Dynamic calendar till day of today)

Step 1: Create the query and Add a Start Date

Before we begin adding any type of code, we need to create the query. For that, we’ll go to Power Query ->  Other Sources -> Blank Query.

That will launch the Power Query Editor and within that window we can add our own code in the formula bar. Inside that bar we add the following code:

=#date(2013,1,1)

We added that in our first step. The name of that step is Source and it holds the value of the start date of our calendar table.

Step 2: The code that does the Magic

Let’s go ahead and create a new custom step by clicking on the fX icon right in the formula bar. This will add a new step called Custom which would reference to the previous step. We would like to change that original code in the formula bar to be this one:

= List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)) 

Here’s the breakdown of that formula:

• Dates – it helps us a create a list of dates and its inputs are
  • A Start Date
  • The number of values to return
  • The increment to add
• Source – as you already know, this is the value of our Start Date so it goes inside the first parameter
• From – we use this function multiple times to transform any type of value into a Number and use it for math operations
• LocalNow – this is the equivalent of NOW() in Power Query so it returns the current date and time. This will make sure that we only get a dynamic range of dates until the present day
• #duration(1,0,0,0) – another literal that adds 1 day (so that our increment is on a daily basis)

Go Ahead and convert that list into a table using the List Tools ribbon. After you convert it into a table, please change the data type of that column to be Date and rename that column to something more descriptive like Date.

Step 3: Adding the date/number-based columns (using the UI)

Next step is to add the much needed columns such as Month Number, Year, Start of the Month Date, End of the Month Date and all that good stuff.

We do that by simply following these steps (please make sure that you select the Date Column):

1. Go to the Add Column ribbon
2. Go to the From Date & Time Group
3. Select the new column that you want from the dropdown

[Chaucer]

Awesome, thanks Ronald!

Any cunning ideas of how to filter since the most recent weekday 5pm?

[Eric_Zhang]

---

@Chaucer wrote:

Awesome, thanks Ronald!

Any cunning ideas of how to filter since the most recent weekday 5pm?

---

@Chaucer

Could you share some sample data and expected output? Even better you can upload the pbix file to OneDrive/GoogleDrive and share the download link. Do remember to mask sensitive data before uploading.

[Chaucer]

Hi Eric,

Thank you so much for looking at this.

So I've stuck a link to some boring data here - Unit Sales

I've knocked up a basic table that shows the sales since yesterday.

What I'd like it to do is show the sales since the most recent 5pm that has occured on a workday. I.e. if it's 3pm on Thursday, I want the sales since 5pm on Wednesday. If it's 6pm on Thursday, I want the sales since 5pm on Thrusday, and if it's 2 pm on Monday, I want the sales since 5pm on Friday.

If that's way over complicated, sales since 5pm the previous day would still be very valuable.

Many many thanks!

Hopefully that makes sense!

Thanks

Stuart

[Vvelarde · Accepted solution]

@Chaucer

Hi, Try this to obtain sales from 5PM of Previous Day

Calculated Column 

1. InvoiceDate-OnlyDate = 'All Invoices'[Invoice Date].[Date]  --Modeling - Format -- To only Date

2. InvoiceDate-Diff =
DATEDIFF('All Invoices'[InvoiceDate-OnlyDate],TODAY(),DAY) * 100 + hour('All Invoices'[Invoice Date])

In level filter add:

Regards

Victor

[Chaucer · Accepted solution]

Hi Victor,

That was great thank you. I made a minor tweak to it as what it was actualy giving me was sales before 5pm yesterday plus sales so far today. 

Changing 

InvoiceDate-Diff = DATEDIFF('All Invoices'[InvoiceDate-OnlyDate],TODAY(),DAY) * 100 - hour('All Invoices'[Invoice Date])

and then filtering for values under 83 worked!

Many thanks on this.