Forum Discussion

andrewb95's avatar
andrewb95
Icon for Helper II rankHelper II
6 years ago
Solved

Take 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 tak...
Need Help
  • v-xicai's avatar
    v-xicai
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    andrewb95
    Icon for Helper II rankHelper II
    6 years ago

    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.