Forum Discussion

BrookeO3's avatar
BrookeO3
Regular Visitor
4 years ago
Solved

PowerBI/Power Query Formulas

Hi, I am trying to create a custom column in PowerBI via power query but I can't seem to get the correct formula. I need to determine if a date is </> 6 months & then have the new column populated with "Current" or "OAI" based on the true/false value. 

So...

 

If {Column w/ Date} >6 months from today then "OAI"

If {Column w/ Date} <6 months from today then "Current"

 

 

Thanks in advance for your help! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi BrookeO3 ,

    You can get the inventory status by adding a custom column in Power Query Editor or DAX, please find the details in the attachment.

    1. Create a custom column in Power Query Editor just as suggested by Anonymous 

    Here it need to make some change in his formula as below, the part with red font is updated one:

    =if [Last fulfillment date] > Date.AddMonths(Date.From(DateTime.LocalNow()), - 6) then "Current" else "OAI"

    Add custom column in Power Query Editor

    2. Create a calculated column as below by DAX

    Column = 
IF (
    DATEDIFF ( 'Table'[Last fulfillment date], TODAY (), MONTH ) > 6,
    "OAI",
    "Current"
)

    Best Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BrookeO3 

    Please use the below code

    = Table.AddColumn(#"Changed Type6", "FinancialYear", each if [DateCaseCreated] < Date.AddMonths(getdate(), -6) then "Current" else "OAI" )

    • BrookeO3's avatar
      BrookeO3
      Regular Visitor
      4 years ago

      Hi, thanks for your response. I am getting an error in the "getdate" part of the formula - I can't figure out what I am missing. 

       

  • BrookeO3's avatar
    BrookeO3
    Regular Visitor
    4 years ago

    Hi again. I think the formula is correct, but I am getting an error in my new column. 

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BrookeO3 ,

    You can get the inventory status by adding a custom column in Power Query Editor or DAX, please find the details in the attachment.

    1. Create a custom column in Power Query Editor just as suggested by Anonymous 

    Here it need to make some change in his formula as below, the part with red font is updated one:

    =if [Last fulfillment date] > Date.AddMonths(Date.From(DateTime.LocalNow()), - 6) then "Current" else "OAI"

    Add custom column in Power Query Editor

    2. Create a calculated column as below by DAX

    Column = 
IF (
    DATEDIFF ( 'Table'[Last fulfillment date], TODAY (), MONTH ) > 6,
    "OAI",
    "Current"
)

    Best Regards