Take a value from a set time.

[andrewb95]

I have a data table like this, it is real time and updates regularly. The value can change at any time. 

Date	Company Name	Value
01.01.2020	A	5
01.02.2020	B	12

I want to take the value at a static time. ie. the first of each month. Then I would have:

Date	Company Name	Value	Value Specific Date
01.01.2020	A	5	4

Then I would be able to see the change in this transactional value to the static value. How do I create a new field which will show the value at a static time? 

[v-xicai · Accepted solution]

Hi @andrewb95 ,

You may create a measure like DAX below, in which the variable _PrevDate is to get the previous date before current max date of table.

Value Specific Date=

var _PrevDate = CALCULATE(MAX('Table'[Date]), FILTER( ALLEXCEPT('Table', 'Table'[Company Name]), 'Table'[ Date] <  MAX('Table'[ Date])))
return

CALCULATE(FIRSTNONBLANK('Table'[Value], 1),  FILTER( ALLEXCEPT('Table', 'Table'[Company Name]), 'Table'[ Date]= _PrevDate))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@andrewb95 , Something like this

measure =
var _date = date(2020,01,01)
return
calculate(sum(Table[Value]), filter(Table,Table[date] =_date))

or

measure =
var _date = date(2020,01,01)
return
calculate(sum(Table[Value]), filter(all(Table),Table[date] =_date))

[andrewb95]

@amitchandak Thanks I think something like this may work but the  "filter(Table,Table[date] =_date)" would not work as I do not have a Table[date] which corrolates to the _date. 

Maybe I can explain in this way. 

The date column I have mentioned above is just a generic signup date to the list and doesn't corrolate to the time I want to take the value. 

So in essence I would like to take the value and populate it into a column when a date matches lets say current date. 

ie. 

Today Company A is value of 5 then tomorrow this value changes to 6. I want to be able to add a column based upon a specific date which will take the value number which would not change. 

As I say so I want to populate the new column with this 'Value' so it doesn't change in the future but from a current date or a date in the future. 

Ie. I can say lets take the value on the 20.09.2020 I want to capture this value column on this date into a new column. Then on the 25.09.2020 I can look at the original value column and then the new value column (value captured on a date) then compare. 

I hope this is clearer.