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:

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

mhsk's avatar
mhsk
Helper II
7 years ago

Thanks for you help, is there any simple way to recreate those 4 measures as calculated columns? (it would improve my performance)