Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

 

 

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

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