# Calculate date from days elapsed

Hi all I am struggling to calculate a date from a date minus number of days elapsed.

I want to calculate the date using today minus days elapsed from a column in the date so that I show a date in my visualisation rather than days elapsed!

Can someone help me out please! I can do it in Excel using Today() - cell ref but can’t get PowerBI to return the date I require!!

Hi @ray,

Please provide a sample PBIX and the desired outcome (in excel)

Plant Duration Created date Storage Section
GB01 74 03/07/2020 001
GB01 5 10/09/2020 001
GB01 111 27/05/2020 001
GB01 5 10/09/2020 001
GB01 6 09/09/2020 001

I am trying to calculate the Created date using today minus the Duration number which is already available in the data. I will then make a line chart. Does that give more clarity?

Hi @ray,

Difficult to know if this solution will work for you based on your exact scenario, but if you have both the date and the days you’d like to minus from the date in columns you can try the below.

Effective Date = SELECTEDVALUE( Test[Date] ) - SELECTEDVALUE( Test[DaystoMinus] )

Edit: Looks like we posted at the same time, let me know if this doesn’t solve your problem.

Hi @ray

`Test = TODAY() - SELECTEDVALUE( 'Table'[Duration])`

Try this.

HI many thanks for the reply. I do not have a date to work from just the duration number. so cannot make this work I assume I need to create a new column but no idea as to the best approach

Is this what you’re looking for? RE: Effective Date 2 (You can add a column into your dataset and put a TODAY() formula)

@ray,

`Creation Date = TODAY() - SELECTEDVALUE( 'myTable'[Duration])`

OR just sort it in Power Query

The issue I have is I do not have the created date, I want to understand how I calculate that from only having the duration number.

In excel I just use Today()-the duration cell ref but am struggling to undertnad how to do the same calc in PowerBI

Hi @ray

Just now updated it. Seems many people replying at same time.

Thank you all, I got there eventually!!

1 Like

To be honest with you, i had a hard time understanding what you really want as you had the created date in your data

thanks
keith

1 Like

Hi Keith,

I calculated the Created Date in Excel as an example of what I was trying to get to. As for solution I used the one suggested by Melissa in the end.

Many thanks to you and everyone for help on the forum

1 Like