Calculate date from days elapsed

DAX DAX Calculations
1

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!!

2

Hi @ray,

Please provide a sample PBIX and the desired outcome (in excel)
I’m confident members here can help you work through this.

3
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?

4

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] )

image

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

5

Hi @ray

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

Try this.

6

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

8

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

image
image751×275 23.3 KB

9

@ray,

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

OR just sort it in Power Query

image
image1077×184 11.3 KB

10

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 :frowning:

11

Hi @ray

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

12

Thank you all, I got there eventually!!

1 Like
13

please post your solution so other can learn from your experience.

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

thanks
keith

1 Like
14

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
15

Can you please mark the thread as solved? Thanks, Greg