Search code examples
powerbiconditional-statementspowerbi-desktop

Power BI conditional column between two dates

I’m trying to insert a conditional column in the Power Query Editor where if a date falls between certain dates, a specific value is added. The dates and values are listed below.

I have attached a screenshot of my attempt, but the results stop at 51 and do no increase from there.

Where am I going wrong with this? It’s almost as if it needs a Between argument, but is this possible in the Power Query Editor?

Dates & Values:

Before 18/07/2022 = 31

On or After 18/07/2022 & Before 08/08/2022 = 51

On or After 08/08/2022 & Before 30/08/2022 = 71

On or After 30/08/2022 = 91

Enter image description here

Solution

  • An if statement searches for the first TRUE() value and returns it. In your case, you use condition "after or equal", so every date with value >=18/07/2022 gets 51, because it is the first True value.

    Try to change the order:

    1. is before 18/07/2022 = 31
    2. after or equal to 30/08/2022 = 91
    3. after or equal to 08/08/2022 = 71
    4. after or equal to 18/07/2022 = 51