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.
I have two unrelated tables and want to calculate the total amount open if transactiondate<datetable[date] and dateallocated is >datetable[date]. Seems pretty simple but I cannot get it to work.
Example below is one transaction. It was created 12/1/2016 and paid 1/12/2016. Therefore in the seperate date table it would show $25 for all of the days in Jan until it was paid on the 12th.
Solved! Go to Solution.
Try
Open Amt = CALCULATE ( SUM ( transactiontable[Amount] ), FILTER ( transactiontable, dateTable[Date Table] >= transactiontable[Transactiondate] && dateTable[Date Table] <= transactiontable[Date allocated] ) )
You need to relate the tables. Based on what I see - there isn't a value to relate. You could add an ID field to both tables and enter the same value on all rows i.e. 1 of the Date table and the single row of the Transaction table. Then join the 2 tables on the ID field. After that you can write DAX that will calculate as you seek.
I am able to get this to work without an issue for a given date:
ARSlicer = CALCULATE(SUM('Accounts Receivable'[Invoice Amount]),FILTER('Accounts Receivable','Accounts Receivable'[TRANSACTION_DATE]=AROpenDates[Date]))
But it seems to break if i try to do something like less than date:
ARSlicer = CALCULATE(SUM('Accounts Receivable'[Invoice Amount]),FILTER('Accounts Receivable','Accounts Receivable'[TRANSACTION_DATE]<AROpenDates[Date]))
Try
Open Amt = CALCULATE ( SUM ( transactiontable[Amount] ), FILTER ( transactiontable, dateTable[Date Table] >= transactiontable[Transactiondate] && dateTable[Date Table] <= transactiontable[Date allocated] ) )
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |