Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Find busy hour value based on different field

    Silver Contributor
    Posted Aug 06, 2019 05:12 AM
    Hi I need support on below

    I have an hourly record of "sales amount" & "quantity"
    my requirement is to show the "sales amount" of the hour when "quantity" is maximum and if possible the (hour or Datetime) of that record

    If the selected period is more than one day then it should show the "sales amount" of the hour from all period
    let's say If selected period is 2 days then an hour should be from 48 hours (Datetime)

    ------------------------------
    Asif
    ------------------------------


  • 2.  RE: Find busy hour value based on different field

    Silver Contributor
    Posted Aug 08, 2019 01:20 AM
    Can anyone please help me

    ------------------------------
    Asif Dakhway
    +96599005086
    ------------------------------



  • 3.  RE: Find busy hour value based on different field

    Top Contributor
    Posted Aug 09, 2019 04:46 AM
    Please can you upload some sample data.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 4.  RE: Find busy hour value based on different field

    Silver Contributor
    Posted Aug 09, 2019 11:06 AM
      |   view attached
    Attached sample data

    ------------------------------
    Asif Dakhway
    +96599005086
    ------------------------------

    Attachment(s)

    xlsx
    sample data.xlsx   17 KB 1 version


  • 5.  RE: Find busy hour value based on different field

    Top Contributor
    Posted Aug 09, 2019 12:42 PM
    Edited by Gopa Kumar Sivadasan Aug 09, 2019 12:43 PM
      |   view attached
    Hi @Asif Dakhway
    Please see if the following helps you. I had worked on this before I saw your sample data. So, I had created my own sample data. But I think the concept should still work with your data also.
    The DAX for the Max Sale Qty measure is below:
    Max Sale Qty =
    ----------------------------------------------------
    --get the maximum qty sold for the selected period ignoring any filter contexts from the visual itself 
    VAR _maxqty =
        CALCULATE ( MAX ( 'Sales Table'[Quantity] ), ALLSELECTED ( 'Sales Table' ) ) 
    -----------------------------------------------------
    --create a virtual table with the rows which satisfy the maxqty. Here also, ignore any filer contexts coming from the visual itself. This table can have more than one row if there are multiple rows for the selected period with the same maximum value
    VAR rlvntable =
        CALCULATETABLE (
            'Sales Table',
            ALLSELECTED ( 'Sales Table' ),
            'Sales Table'[Quantity] = _maxqty
        ) 
    -----------------------------------------------------
    --since the virtual table may contain more than 1 row due to same max qty in different rows, we identify the maximum timestamp from the virtual table. 
    VAR _maxTimeStamp =
        CALCULATE ( MAX ( 'Sales Table'[TimeStamp] ), rlvntable ) 
    -----------------------------------------------------
    RETURN
        //have a check for the maxtime timestamp calculated above to return only that row in the visual
        IF (
            SELECTEDVALUE ( 'Sales Table'[TimeStamp] ) = _maxTimeStamp,
            1,
            BLANK ()
        )
    ​

    ​PFA the pbix file also for your reference.

    ------------------------------
    Gopa Kumar S
    Limner Consulting
    ------------------------------



  • 6.  RE: Find busy hour value based on different field

    Silver Contributor
    Posted Aug 14, 2019 03:39 AM
    Thanks Gopa Kumar,

    ------------------------------
    Asif Dakhway
    +96599005086
    ------------------------------



  • 7.  RE: Find busy hour value based on different field

    Posted 16 days ago
    Hi, I tried this to get the Busiest Day based on space utilization and getting an error. Im trying figure out what could be wrong in the DAX.


    ------------------------------
    Nam JAl
    ------------------------------