cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Multiple IF Conditions in Custom Column with dates

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

 

 

 

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

@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
Not applicable

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)))

cap1.JPG


 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors