Forum Discussion

CaroReglisse's avatar
CaroReglisse
New Member
2 years ago
Solved

Creating a column retrieving the closest previous date with conditions (Example shown)

Hi all,  I am looking to create a new column in PowerBI/PowerQuery whose value will depend on 3 different columns.  This is the example dataset I have :          New columns I need ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi CaroReglisse 

     

    If you are happy with a DAX method, you can create a new column with the following DAX formula:

    Column = IF('Table'[Type]="B",
VAR curDate = 'Table'[Date]
VAR preDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date]<curDate, ALL('Table'))
VAR preDateRow = CALCULATETABLE('Table','Table'[Date]=preDate, ALL('Table'))
VAR preType = MAXX(preDateRow,'Table'[Type])
VAR preRowNumber = MAXX(preDateRow,'Table'[Run number])
RETURN IF(preType<>"B", preRowNumber, 'Table'[Run number]),
'Table'[Run number])

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi CaroReglisse 

 

If you are happy with a DAX method, you can create a new column with the following DAX formula:

Column = IF('Table'[Type]="B",
VAR curDate = 'Table'[Date]
VAR preDate = CALCULATE(MAX('Table'[Date]), 'Table'[Date]<curDate, ALL('Table'))
VAR preDateRow = CALCULATETABLE('Table','Table'[Date]=preDate, ALL('Table'))
VAR preType = MAXX(preDateRow,'Table'[Type])
VAR preRowNumber = MAXX(preDateRow,'Table'[Run number])
RETURN IF(preType<>"B", preRowNumber, 'Table'[Run number]),
'Table'[Run number])

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!