DAX Expression for date minus 6 months

[Anonymous]

Hello,

I am trying to display the Vendor that is about to expire 6 months from today.

For example,

Start Date	End Date	Date of Renewal
Jan. 1, 2021	Dec. 30, 2021	July 1, 2021

I want to show from another table the Date of Renewal. I do have the formula from excel ( =EDATE(E3,-6) ) but I don't know how to convert it into a DAX expression. I've created one but it's not working.

Please I need your help. Thank you.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download your PBIX file here

Based on the data in your PBIX you have Service Providers that have already expired and some that are expiring in the next 6 months.

Using this measure you can see from a table the status of each vendor

Renewal = 

VAR _DateDiff = DATEDIFF(TODAY(), SELECTEDVALUE('Vendor Data'[END]), MONTH)

RETURN

SWITCH(
    
    TRUE(),
    
    _DateDiff < 0 , "Expired",
    
    _DateDiff < 6 , "Expiring",

    "OK"
)

[Anonymous]

Thank you @PhilipTreacy , your measure helped a lot. I'll use and study it. Again thank you so much.

[PhilipTreacy]

@Anonymous 

OK so are you trying to highlght every vendor that will expire within 6 months?

Regards

Phil

[Anonymous]

Yes, that's right. I am trying to return all vendors who are expiring.

Thank you.

[PhilipTreacy]

Hi @Anonymous 

The 'Vendor Data'[END] column needs to be type Date.

You can see in your screenshot that it isn't.

Regards

Phil

[Tahreem24]

@Anonymous ,

From you screen shot, Vendor Date column should be of Date format. Try to make this column to Date Type like below Screen shot:

Select the Vendor Date then do the below formatting.

[Anonymous]

Hello @Tahreem24 ,

Thank you so much for this, I did what you told me to convert the text into a Date data type but unfortunately it returns the same error.

[PhilipTreacy]

Hi @Anonymous 

You haven't set your start date and end date columns to data type Date.

In Data view select the column(s) then change the Data type from the Ribbon

Are you trying to return just a single vendor or all vendors who are expiring?

Can you please share your PBIX file, or at least copy/paste some sampledata, so I understand the structure of your data.

Regards

Phil

[Anonymous]

Hello @PhilipTreacy ,

Thank you so much for this, I did convert it into a Date data type but it still has the same error.

Here's the pbix file

https://drive.google.com/file/d/10ZzzViZY3txGd1cqj1YOo2uVTxq3k6Bb/view?usp=drivesdk 

im sorry i dont know how to attached a pbix file here directly