Forum Discussion

Chaucer's avatar
Chaucer
Icon for Helper II rankHelper II
8 years ago
Solved

Units Sold Since 5pm Yesterday

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's avatar
    Vvelarde
    8 years ago

    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's avatar
    Chaucer
    8 years ago

    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.

8 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago
    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's avatar
      Chaucer
      Icon for Helper II rankHelper II
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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