Forum Discussion
Helper IIFinding 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 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.
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
5 Replies
v-yulgu-msftMicrosoft Employee
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?