Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
@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))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |