Forum Discussion

mhsk's avatar
mhsk
Helper II
7 years ago
Solved

Finding data based on closest to today calculation

Hi Team,   Its problaby sth you will answer on fly. Thanks for all contributions.   I have to find and show in a table just one row with closest to today data(mrp_qty and mrp_due_date) with categ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi mhsk,

     

    Please refer to below measures:

    Next order =
CALCULATE (
    MIN ( 'Data Table'[date] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
            && 'Data Table'[date] >= TODAY ()
            && 'Data Table'[type] = "order"
    )
)

Qty of order =
CALCULATE (
    SUM ( 'Data Table'[qty] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[date] = [Next order]
            && 'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
    )
)

Next Demand =
CALCULATE (
    MIN ( 'Data Table'[date] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
            && 'Data Table'[date] >= TODAY ()
            && 'Data Table'[type] = "demand"
    )
)

Qty Demand =
CALCULATE (
    SUM ( 'Data Table'[qty] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[date] = [Next Demand]
            && 'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
    )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi mhsk,

 

Please refer to below measures:

datediff =
ABS ( DATEDIFF ( SELECTEDVALUE ( Table3[mrp_due_date] ), TODAY (), DAY ) )

rank =
RANKX ( ALLSELECTED ( Table3 ), [datediff],, ASC, DENSE )

Add measure [rank] to "visual level filters" and set its value to 1.

Best regards,

Yuliana Gu

  • mhsk's avatar
    mhsk
    Helper II
    7 years ago

    Hi , thanks for prompt reply

     

    how to make it not using Visualisation filtering? (just dax that will return me 1 value (with measure) for closest date later than today and measure showing just 1 value that will be  mrp_qty closest to today but after today ?

     

    thanks in advance

    • mhsk's avatar
      mhsk
      Helper II
      7 years ago

      just to clarify i need to have table with just 1 row (for 1 part nr) with both NEXT (closest to today or today) ORDER  and NEXT (closest to today or today) DEMAND for each PART NUMBER.

       

      my DATA TABLE looks more or less like this:

       

      Today is 19.12.2018

       

      part nrdatetypeqty
      123417.12.2018order50
      123421.12.2018order10
      123431.12.2018order5
      123419.12.2018demand1
      123423.12.2018demand6
      123430.12.2018 demand10

       

      what i am looking for is:

      part nrNext orderQty of orderNext DemandQty Demand
      123421.12.20181019.12.20181

       

      i cant get it with rank and visual level filters, is there a way to write it in dax?

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi mhsk,

         

        Please refer to below measures:

        Next order =
CALCULATE (
    MIN ( 'Data Table'[date] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
            && 'Data Table'[date] >= TODAY ()
            && 'Data Table'[type] = "order"
    )
)

Qty of order =
CALCULATE (
    SUM ( 'Data Table'[qty] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[date] = [Next order]
            && 'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
    )
)

Next Demand =
CALCULATE (
    MIN ( 'Data Table'[date] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
            && 'Data Table'[date] >= TODAY ()
            && 'Data Table'[type] = "demand"
    )
)

Qty Demand =
CALCULATE (
    SUM ( 'Data Table'[qty] ),
    FILTER (
        ALLSELECTED ( 'Data Table' ),
        'Data Table'[date] = [Next Demand]
            && 'Data Table'[part nr] = SELECTEDVALUE ( 'Data Table'[part nr] )
    )
)

         

        Best regards,

        Yuliana Gu