Forum Discussion
CALCULATE with Filter not working
- 4 years ago
Hi Draszor
Here is your sample file with the solution https://www.dropbox.com/t/MEcfn1F5U6wFM3aO
I have created "Inactive" relatioships between date tables and the fact table.
With this we can just activate the required relationship only when needed.
The code is little long but pretty simple. Just need to check when fact table is empty on the check conditions (at the selected date) if at either D1 or D2 the table has no raws then the formula retruns blank otherwise the difference calculation is curried out.
Same fore amount calculation, we calculate the sum by activating the the relationship with the designated date table and filter the results fro only Actual Costs.Actual Cost Difference = VAR AmountD1Actual = CALCULATE ( SUM ( Sheet1[Amount] ), USERELATIONSHIP ( Sheet1[Date], Date1[DATE1] ), Sheet1[Amount Type] = "Actual Costs" ) VAR AmountD2Actual = CALCULATE ( SUM ( Sheet1[Amount] ), USERELATIONSHIP ( Sheet1[Date], Date2[DATE2] ), Sheet1[Amount Type] = "Actual Costs" ) VAR CheckAmountD1 = CALCULATE ( COUNTROWS ( Sheet1 ), USERELATIONSHIP ( Sheet1[Date], Date1[DATE1] ), ALLEXCEPT ( Sheet1, Sheet1[Project], Sheet1[Date] ) ) VAR CheckAmountD2 = CALCULATE ( COUNTROWS ( Sheet1 ), USERELATIONSHIP ( Sheet1[Date], Date2[DATE2] ), ALLEXCEPT ( Sheet1, Sheet1[Project], Sheet1[Date] ) ) VAR Result = IF ( OR ( ISBLANK ( CheckAmountD1 ), ISBLANK ( CheckAmountD2 ) ), BLANK (), AmountD2Actual - AmountD1Actual ) RETURN ResultBy the way, using relationships deliveres the best performance among all other options.
You report shall look like this
Please let me know if this answeres your query. Have a great day!