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.
Hello everyone,
I am trying to achieve something that shouldn't be really hard, but I'm not quite capable with LOOKUPVALUE.
I have three tables:
OrderTable, where I have ShippingDate of several articles.
ContractRows, where I have the amount of article that need to be sold.
ContractHeader, where I have information about the contract.
The tables are linked together this way:
[OrderTable]1 - *[ContractRows]1 - *[ContractHeader]
So, for every Order in ContractHeader I have information about what and how much must be sold in ContractRows, and in OrderTables I have information about what has been shipped.
What I'd like to have is a calculated column in ContractHeader, where I have a flag if there has been atleast an order in the past six months.
I guess I should look for the dates in OrderTables linked to the same contract number, extract the max one related to the same contract and check if the date is in the last six months.
My biggest problem is how to extract the maximum order date related to the same contract (if there is one), I should be able to perform the rest.
Maybe I don't even have to use LOOKUPVALUE, but I'm not sure.
May give me a hint?
Many thanks!
Solved! Go to Solution.
Hi MaxItaly,
Create a calculate column and try this pattern:
Flag = IF ( COUNTROWS ( FILTER ( RELATEDTABLE ( OrderTable ), OrderTable[date] >= EDATE ( TODAY (), -6 ) && OrderTable[date] <= TODAY () ) ) >= 1, "flag", BLANK () )
Regards,
Jimmy Tao
Nevermind, I guess it was easier than I thought:
may be
CALCULATE(MAX(ShipDate); RELATEDTABLE(OrderTable)) correct?
Hi MaxItaly,
Create a calculate column and try this pattern:
Flag = IF ( COUNTROWS ( FILTER ( RELATEDTABLE ( OrderTable ), OrderTable[date] >= EDATE ( TODAY (), -6 ) && OrderTable[date] <= TODAY () ) ) >= 1, "flag", BLANK () )
Regards,
Jimmy Tao
Thanks, this is good as well, and it includes the second part of my request.
Thanks!
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |