Multiple If statements or Calculate?

[zubairs]

Hi Guys,

So I am trying to create measure that uses multiple conditions. I am trying to calculate the net sales value based upon the discount and the shel life of the product. So the first step is to get the sales as per the shelf life.

For the shelf life, I have two categories. The long life which is greater (>) than 28 days and short life which is less than (<) 28 days. This is the formula in excel:

=Gross Sales*(31/shelf life days)

I have created two tables, one with the shelf life and discount %. The other with the sales and sales returns data. The relationship between the two table is through the brand name, which is common between the two tables.

So I want to create the measure with the following conditions:

1 - Go through the sales table, look for "[Gross Sales]"

2 - Than go to the discount table, match the "[Gross Sales]" of that brand with the "[Shelf life Days]"

3 - If the Shelf life days is >28 days than give me the Gross sales of that brand

4 - If the Shelf life days is <28 days than multiply the "Gross Sales" of that brand with (31/[Shelf life days] of that brand) 

[zubairs]

Thanks for the response although this is the error I am getting:

[BA_Pete]

Try changing SELECTEDVALUE to MAX.

Pete

[BA_Pete]

Hi @zubairs ,

I think something more along these lines should work:

_grossSalesAdjusted =
VAR __shelfDays = SELECTEDVALUE(Shelf_life[Days])
RETURN
SWITCH(
  TRUE(),
  __shelfDays > 28,
  CALCULATE(
    SUM(IFRS15_returnspolicy[Value]),
    IFRS15_returnspolicy[Attribute] = "GrossSales"
  ),
  __shelfDays <= 28,
  CALCULATE(
    SUM(IFRS15_returnspolicy[Value]),
    IFRS15_returnspolicy[Attribute] = "GrossSales"
  ) * (31 / __shelfDays)
)

Pete

[zubairs]

The measure is accepted now but it is showing FALSE.