PowerBI/Power Query Formulas

[BrookeO3]

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! 

[v-yiruan-msft · Accepted solution]

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 @pranit828 

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

[BrookeO3]

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

[pranit828]

Hi @BrookeO3 

Please use the below code

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

[BrookeO3]

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. 

[smpa01]

@BrookeO3  replace getdate with Date.From(DateTime.LocalNow())