Finding data based on closest to today calculation

[mhsk]

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 category Purchase Order. i tried top n 1  and datediff, but no effects. Could you please advise how to go from whole table to table showing mi only what is marked yellow. I will use it dinamic so Today() will be needed.

[v-yulgu-msft]

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]

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]

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 nr	date	type	qty
1234	17.12.2018	order	50
1234	21.12.2018	order	10
1234	31.12.2018	order	5
1234	19.12.2018	demand	1
1234	23.12.2018	demand	6
1234	30.12.2018	demand	10

what i am looking for is:

part nr	Next order	Qty of order	Next Demand	Qty Demand
1234	21.12.2018	10	19.12.2018	1

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

[v-yulgu-msft · Accepted solution]

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]

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