Specify OR condition to filter by 2 columns in Calculate function

[Anonymous]

I am working on the latest Power BI Desktop (Oct 2021 update) and trying to create a `Clustered Column Chart` to display 2 measures viz., one for SUM(USD_Amount__c), other for COUNT(USD_Amount__c).

I have a Date table named `dimDate` which has a Year offset column named `rYear`. There is a FACT table which contains 2 Date columns viz., `CreatedDate` & `ClosedDate`. The FACT table also contains the `USD_Amount__c` column.

The dimDate table is joined by a relationship to both the CreatedDate & CloseDate columns of the FACT Table like this:

dimDate[Date] -->FACT[CreatedDate]

dimDate[Date]-->FACT[CloseDate]

I want to do something like this to get the 2 measures:

Amt($) = SUM the USD_Amount__c in the FACT table by filtering it on the CreatedDate & CloseDate such that, the CreateDate OR CloseDate are >=2019/01/01 i.e. starting 2 years ago. I dont want to hardcode the date 2019-01-01.

Amt(#) = COUNT the USD_Amount__c in the FACT table by filtering it on the CreatedDate & CloseDate such that, the CreateDate OR CloseDate are >=2019/01/01 i.e. starting 2 years ago. I dont want to hardcode the date 2019-01-01.

Right now, i am trying to use the rYear column in dimDate table to filter the FACT table like this:

Opps(#) IT = CALCULATE(COUNT('FACT'[USD_Amount__c]), CALCULATETABLE ( dimDate, dimDate[rYear] >= -2), USERELATIONSHIP(dimDate[Date],'FACT'[CloseDate]))

Opps($) IT = CALCULATE(SUM('FACT'[USD_Amount__c]), CALCULATETABLE ( dimDate, dimDate[rYear] >= -2 ), USERELATIONSHIP(dimDate[Date],'FACT'[CloseDate]))

This works, but i cannot specify an OR condition adding 'FACT'[CreatedDate] along with 'FACT'[CloseDate].

[AlexisOlson]

I'd start with something like this and adjust as needed:

Opps(#) IT =
VAR StartDate = DATE ( MAX ( dimDate[rYear] ) - 2, 1, 1 )
RETURN
    CALCULATE (
        COUNT ( 'FACT'[USD_Amount__c] ),
        FILTER ( 'FACT', 'FACT'[CloseDate] >= StartDate || 'FACT'[CreatedDate] >= StartDate )
    )

[Anonymous]

Hi @AlexisOlson ,

Thank you for your quick response. 

The dimDate[rYear] is a Year offset viz., 1 for next year, 0 for current year, -1 for last year, -2 for prior to last year etc.

Any idea how that can be incorporated in your above formula?

[AlexisOlson]

How about YEAR ( MAX ( dimDate[Date] ) ) or YEAR ( TODAY () ) instead of MAX ( dimDate[rYear] )?

If you have an actual year column instead of an offset column, you could use that.

[Anonymous]

Yes, i have an actual year column.

BTW, YEAR( MAX( dimDate[Date] )) - 2 gives me 2021 as i think the Date table extends to 2023 and so do the CloseDate or CreatedDate columns in FACT table extend to greater than current year. I think what i need is Current Year - 2, isn't it so?

But i do want to make use of the relationship between dimDate and FACT table with Date Slicers. Therefore i need the dimDate table instead of YEAR(TODAY()). How do i do it in your formula?

DimDate does have YEAR column.

[AlexisOlson]

Try what I initially suggested with dimDate[Year] (or whatever your actual year column is called) instead of dimDate[rYear].