Multiple IF Conditions in Custom Column with dates

[Anonymous]

Hi  all,

I need to create a conditional columns that evaluates by date and country columns.

here below is the logic (not in correct dax syntax)

If  (dates > 31.12.2018)then include all countries

If (dates is between 01.01.2018 and 31.12.2018) and country is = "Brazil" or "Canada" then 0

Otherwise select column "revenue"

in plain language:

Create column that display revenue and include all countries for 2019 going forward

If countries are equal to brazil and/or canada for 2018 then return a 0 value.

hope it makes sense.

best regards

[v-yuta-msft]

@Anonymous ,

You can create a calculate column using DAX like pattern below:

Result =
IF (
    YEAR ( Table[Date] ) > 2018,
    CALCULATE (
        SUM ( Table[Revenue] ),
        FILTER ( ALL ( Table ), Table[Date] > DATE ( 2018, 12, 31 ) )
    ),
    IF (
        YEAR ( Table[Date] ) = 2018
            && ( Table[Country] = "Brazil"
            || Table[Country] = "Canada" ),
        0,
        Table[Revenue]
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yuta-msft 

I am not able to select the country or date column as your formula suggest.

The exact formula i am dealing with is showing accumulated revenue for a specific product group vs its budget which is (selected=LQT/AMZ). But there was no budget for brazil and canada thus, they need to be excluded from 2018, but included in 2019.

Some info to explain formula so far:

If i dont make a ifblank statement for revenue then the line graph will show the revenue equal to the lastest period for all periods going forward.
I also have to multiply by 1000 because thats how revenue is recorded and to display million in the Y-axis i need to have the real revenue number not in thousands.

Then its summing all revenue between launch date and last date.

RMIN is the revenue.

original (working formula)

LTD Rev. Acc. (append) without blanks (not in 1000) =

VAR LDate_new =LASTDATE('Append table'[Date])

RETURN

if(ISBLANK(SUM('Append table'[RMIN])),BLANK(),CALCULATE(SUM

    ('Append table'[RMIN]),DATESBETWEEN('Append table'[Date],[Launch Month of Bohrium 2],LDate_new))

)*1000

modified formula based on your suggestion

LTD Rev. Acc. (append) without blanks (not in 1000) =

VAR LDate_new =LASTDATE('Append table'[Date])

RETURN

IF(YEAR(('Append table'[Date] )) > 2018,

if(ISBLANK(SUM('Append table'[RMIN])),BLANK(),CALCULATE(SUM

    ('Append table'[RMIN]),DATESBETWEEN('Append table'[Date],[Launch Month of Bohrium 2],LDate_new)

)*1000

,

IF (

YEAR ( 'Append table'[Date] ) = 2018

&& ('Append table'[* Country New1]= "Brazil"

|| 'Append table'[* Country New1]= "Canada" ),

0,

CALCULATE(SUM

    ('Append table'[RMIN]),DATESBETWEEN('Append table'[Date],[Launch Month of Bohrium 2],LDate_new)

)*1000)))

[Anonymous]

Maybe there is a easier way to just say if year = 2018 then exclude brazil+canada else include all ?