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,
i'm pretty new with DAX language.
I have two databases, A and B
A gives me the code of the item and the "receipt dates" and related quantities in that date;
B gives me the same codes of items and a column with the date.
I have to calculate the sum of quantities from the first receipt date until the "date" given for that specific item.
Consider that:
- in database A the item code can be repeated for more than one date (that's why i want the sum of those dates until the date specified in database B
- th items code are not in the same order among the two databases and in database B there could be as well the code repeated in different dates.
Any suggestion?
thank you very much
Solved! Go to Solution.
Hi @camillasperanza ,
Please provide more detail informations to help us clarify your data structure to test and coding formula.
You can also try to use following measure if it works for your scenario:
Measure = VAR selectedItem = ALLSELECTED ( TableB[Code] ) VAR selectedDate = MAX ( Date[Date] ) VAR _dateList = CALCULATETABLE ( VALUES ( TableA[Receipt Date] ), FILTER ( ALLSELECTED ( TableA ), [Code] IN selectedItem && [Receipt Date] <= selectedDate ) ) RETURN CALCULATE ( SUM ( Table[Quantity] ), FILTER ( ALLSELECTED ( Table ), [Date] IN _dateList ) )
Regards,
Xiaoxin Sheng
Hi @camillasperanza ,
Please provide more detail informations to help us clarify your data structure to test and coding formula.
You can also try to use following measure if it works for your scenario:
Measure = VAR selectedItem = ALLSELECTED ( TableB[Code] ) VAR selectedDate = MAX ( Date[Date] ) VAR _dateList = CALCULATETABLE ( VALUES ( TableA[Receipt Date] ), FILTER ( ALLSELECTED ( TableA ), [Code] IN selectedItem && [Receipt Date] <= selectedDate ) ) RETURN CALCULATE ( SUM ( Table[Quantity] ), FILTER ( ALLSELECTED ( Table ), [Date] IN _dateList ) )
Regards,
Xiaoxin Sheng
Really helps to have example data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |