cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eddy_wright Frequent Visitor
Frequent Visitor

Getting a value from dynamic date

I'm hoping somebody can help. I have seen a lot of people trying to do something similar but every solution i have tried doesn't seem to work for me. 

 

What I am trying to achieve:

I have a table which contains a list of capcity data for some vmware datastores. The data currently gets added in roughly every 5 minutes or so. 

For each datastore name I would like to add a calculated column which finds the "Used" amount which is closes to the date shown in the "7days" column. If i can do this without the requirement for the 7days column then that would be ideal. 

 

Example data:

NameVMCountTotalFreeUsedPercentDateTimeLatestDateIsLatest7days
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:30:0614/04/2019 18:30:06107/04/2019 18:30
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:25:0514/04/2019 18:30:06007/04/2019 18:25
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:20:0614/04/2019 18:30:06007/04/2019 18:20
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:15:0514/04/2019 18:30:06007/04/2019 18:15
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:10:0514/04/2019 18:30:06007/04/2019 18:10
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:05:0614/04/2019 18:30:06007/04/2019 18:05
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:00:0714/04/2019 18:30:06007/04/2019 18:00
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:55:0514/04/2019 18:30:06007/04/2019 17:55
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:50:0614/04/2019 18:30:06007/04/2019 17:50
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:45:0514/04/2019 18:30:06007/04/2019 17:45
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:40:0614/04/2019 18:30:06007/04/2019 17:40
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:35:0614/04/2019 18:30:06007/04/2019 17:35
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:30:0714/04/2019 18:30:06007/04/2019 17:30

 

This goes on for many days worth of data. I haven't included it all because the example would be huge. 

 

Example desired output:
For this example lets assume that 7 days ago we had 7Gb used and then at -7days at 07/04/2019 17:40:06 we had only 6 GB used. 

 

NameVMCountTotalFreeUsedPercentDateTimeLatestDateIsLatest7daysused
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:30:0614/04/2019 18:30:0617
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:25:0514/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:20:0614/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:15:0514/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:10:0514/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:05:0614/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 18:00:0714/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:55:0514/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:50:0614/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:45:0514/04/2019 18:30:0607
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:40:0614/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:35:0614/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:30:0714/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:25:0514/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:20:0714/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:15:0614/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:10:0614/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:05:0614/04/2019 18:30:0606
R-RG-STR02-LUN203-REPL-LOGS010288014/04/2019 17:00:0714/04/2019 18:30:0606

 

I hope i've explained that well, if anyone could help that would be appreciated 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Getting a value from dynamic date

@eddy_wright ,

 

You may try using RANKX.

https://community.powerbi.com/t5/Desktop/IF-AND-dax-Formula/m-p/293991#M129654

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
eddy_wright Frequent Visitor
Frequent Visitor

Re: Getting a value from dynamic date

Hi, 

 

Thanks for the reply. I tried to adapt the example you showed to my data, it seemed to crash my machine, i'm guessing due to the amount of data I have in there and the fact it is trying to run this on every row. 

 

Code i tried is:

Column =
IF (
     RANKX (
          FILTER ( Tbldatastore, Tbldatastore[DateTime] < EARLIER ( Tbldatastore[7days] )
          && Tbldatastore[name] = EARLIER (Tbldatastore[Name]) ),
     Tbldatastore[Index],
      ,
      ASC
      )
= 1,
Tbldatastore[Used],
0
)