Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
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?
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
Thanks for you help, is there any simple way to recreate those 4 measures as calculated columns? (it would improve my performance)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |