Turning text values into dates for additional reporting / analysis

[MD72]

Hello

I have a column in my data model titled 'Horizon_Timeframe' as shown below. The source (SharePoint List) is a drop-down list with 3 options: <1 Year; 1-2 Years; >2 Years. 

I would like to create a new calculated column, in Power BI Desktop, that allows me to report / analyse using a real date range, using today's date as the starting point, but not sure where to begin, so any guidance is very much appreciated.

Regards

Mark

Mark

[MD72]

Hi janigaurang,

Brilliant, that works just how I wanted.

Regards

Mark

[MD72]

Hi Janigaurang

Thanks for the response. Think I understand what you are saying. There is the 'Created' date that is a standard SharePoint column, do you think that this could be used?

Regards

[janigaurang · Accepted solution]

Hi @MD72 

Yes, using that filed, you can definitely create one custom filed.

I have tried with your scenario and result as below:

Formula I used :

TimeFrame = IF((TODAY() - 'FO Backlog v2 0'[Created])<365,"< 1 Year",IF((TODAY() - 'FO Backlog v2 0'[Created])<630,"< 1-2 Year","> 2 Year"))

Let us know if this helps.

Thanks,

[janigaurang]

Hi @MD72 

Your data does not have any field that provide exact date of transaction. For calculated fields, you must need a field where you can do the calculation. Example, if you have a field as transaction date or Historical date, you can do the comparison with today's date in your report.

Please let us know if this helps.

Thanks,