Forum Discussion
Helper IITake a value from a set time.
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?
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.
3 Replies
v-xicaiCommunity Support
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
Super User
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))
andrewb95amitchandak 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.
