How to use if else with two conditions using DAX

[NewbeePowerBI]

Hi,

I am new to Power BI and I have data like this in sales table. I want to create a new column Max_price_new where 0 should be replaced by the Max_price of earlier quarter.

e.g. This is the input table

article	yr&qua	quarter	Max_price	year
10026858	2017Qtr 2	Qtr 2	0	2017
10026858	2017Qtr 1	Qtr 1	19537	2017
10026858	2016Qtr 3	Qtr 3	19537	2016
10026858	2016Qtr 1	Qtr 1	19537	2016

output table should look like:

article	yr&qua	quarter	Max_prise	year	Max_Price_new
10026858	2017Qtr 2	Qtr 2	0	2017	19537
10026858	2017Qtr 1	Qtr 1	19537	2017	19537
10026858	2016Qtr 3	Qtr 3	19537	2016	19537
10026858	2016Qtr 1	Qtr 1	19537	2016	19537

I have already written few formulas to find out:

1. Quarter number where the Max_Price is 0.

2. Find the earlier quarter number.

But I am struggling with how to find price of earlier quarter. i.e. in it should be something like

Max_Price_new = Max_Price where article= current article value and quarter = earlier quarter

How should I write this using DAX ?

[Anonymous · Accepted solution]

Try using  ALLEXCEPT ( Sheet1[Article] ) instead of  ALL ( Sheet1 ).

[v-yulgu-msft · Accepted solution]

Hi @NewbeePowerBI,

Please refer to below calculated columns:

Quarter Num = RIGHT(Sheet1[quarter],1)

Last quarter =
IF (
    Sheet1[Max_prise] = 0,
    CALCULATE (
        MAX ( Sheet1[Quarter Num] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[article], Sheet1[year] ),
            Sheet1[Quarter Num] < EARLIER ( Sheet1[Quarter Num] )
        )
    ),
    Sheet1[Quarter Num]
)

Max_prise New =
LOOKUPVALUE (
    Sheet1[Max_prise],
    Sheet1[article], Sheet1[article],
    Sheet1[year], Sheet1[year],
    Sheet1[Quarter Num], Sheet1[Last quarter]
)

Best regards,
Yuliana Gu

[NewbeePowerBI]

Hi @v-yulgu-msft  Thanks for the solution. It worked! 

[Anonymous]

You say you found a way to get the previous quarter. In my example I have a sequence number for the quarters with 1 starting at the earliest and 4 being the latest.

I also add 1 to each price, so you can see that you are getting the correct values.

You could create a statement like this to get the previous quarter value:

PreviousQuarterMaxPrice = VAR QuarterSequence =
    MAX ( Sheet1[QuarterSequence] )
RETURN
    CALCULATE (
        MAX ( Sheet1[Max_price] ),
        Sheet1[QuarterSequence]
            = QuarterSequence - 1,
        ALL ( Sheet1 )
    )

article	YearQuarter	quarter	year	Max_price	QuarterSequence	PreviousQuarterMaxPrice
10026858	2017Qtr 2	Qtr 2	2017	0	4	19537
10026858	2017Qtr 1	Qtr 1	2017	19537	3	19538
10026858	2016Qtr 3	Qtr 3	2016	19538	2	19539
10026858	2016Qtr 1	Qtr 1	2016	19539	1

[NewbeePowerBI]

Hi, Thank you for your solution.

Removing  MAX from this MAX ( Sheet1[QuarterSequence] ) in your query gave me half the answer of my question.

But I also have many articles like this in the column "article". How to add article condition into query? I want each article's value to be displayed

e.g.

article	yr&qua	quarter	Max_prise	year	Max_Price_new
10026858	2017Qtr 2	Qtr 2	0	2017	19537
10026858	2017Qtr 1	Qtr 1	19537	2017	19537
10026858	2016Qtr 3	Qtr 3	19537	2016	19537
10026858	2016Qtr 1	Qtr 1	19537	2016	19537
10026777	2016Qtr 2	Qtr 2	0	2016	10
10026777	2016Qtr 1	Qtr 1	10	2016

[Anonymous · Accepted solution]

Try using  ALLEXCEPT ( Sheet1[Article] ) instead of  ALL ( Sheet1 ).

[NewbeePowerBI]

Hi @Anonymous Using ALLExcept and adding condition for checking year, solved my problem.