Forum Discussion

Draszor's avatar
Draszor
Icon for Helper III rankHelper III
4 years ago
Solved

CALCULATE with Filter not working

Hi,   I attach the LINK to the example file where I recreated the problem.   I created the table (DIFF TABLE) that should show: 1. difference between the amounts from 2 chosen dates 2. only for...
  • tamerj1's avatar
    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
        Result

    By 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!