IF function + dates

[Anonymous]

Hi guys,

Here's what I'm trying to do....

I have a table with a due date column, I want a column that returns me a date that is my "due date" + 180 days. I would call this column "provision".

The format of this column should be "mm/yyyy"

But if my "provision date" is higher than the current month, than I want that the return is "empty / blank"

I'm kind of newbie in power BI / power query....if you could help me, I would appreciate

Thanks

[Anonymous]

Hello everyone,

Could you help me?

 With the data set below I want to create a column that gives me the month/today as "Dta Prov 2", but that has as result all invoices that has "Dta Provisiao" = month/today and all invoices with "Dta Provisao" < month/today with "Provision" = "0"

Name	Invoice	Pending	Provision	Billdate	Dta Provisao
ipsum	53101609	0,00	738,00	31/07/2005	01/2006
lorem	63101733	0,00	2.586,00	30/06/2005	01/2006
alfa	11145483	18.625,00	0,00	31/05/2018	12/2018
beta	11145728	0,00	3.895,00	31/05/2018	12/2018
omega	11146886	4.207,00	0,00	31/07/2018	02/2019
charlie	11147044	0,00	6.000,00	31/07/2018	02/2019
gamma	11147697	500,00	0,00	31/08/2018	03/2019
delta	11147698	4.000,00	0,00	31/08/2018	03/2019

I was trying the combination of "IF" and "AND", but with no results, so far...

Thank you,

Irene

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Column =
IF (
    YEAR ( DATEADD ( Tab_1[DueDate].[Date], 180, DAY ) ) = YEAR ( TODAY () )
        && MONTH ( DATEADD ( Tab_1[DueDate].[Date], 180, DAY ) ) > MONTH ( TODAY () ),
    BLANK (),
    FORMAT ( DATEADD ( Tab_1[DueDate].[Date], 180, DAY ), "mm/yyyy" )
)

Best regards,

Yuliana Gu

[Anonymous]

Hi @Anonymous

As no sample data given, i didnt test the formula but it should work.

IF(MONTH( Table1[Provision_Date) > MONTH( TODAY()),FORMAT(DATEADD(Table1[DueDate],180,DAY),"mm/yyyy"),BLANK())

Thanks
Raj