Add a value to a column depending on a date.

[andrewb95]

I have a table with two columns. Column a is a list of unique identifiers then column b is a list of values. Every day I refresh the table and the values are changing. 

I want to be able to add three new columns. 

- Value as of creation date (of the record)

- Value as of the first of the last month (example if today is 16/09/2020 - It would capture the value from 01/08/2020)

- Value as of the first of this month (example if today is 16/09/2020 - it would capture the value from 01/09/2020) 

Because column B is always changing I want to see what it was at specific milestones.

[jthomson]

So you've refreshed it today - if all you have is an identifier and a value, which I assume is the value right now, where would you find what the value was at the start of the month? Do you actually have a third column which has a date in it? Do you actually have more than one file showing values at different points in time? Because unless you've stored what the value was at previous points in time somewhere, you're probably out of luck

[andrewb95]

@jthomson exactly when i refresh it will show the value as of now and the value as of before the refresh will be eliminated. 

I do not have the data against the date of change. 

I understand for the origin value this maybe impossible. But do you think it maybe possible lets say going forward if I was to say the value on the first of the next month. 

So I can lets say create a column which stores the value as of 01/10/2020 00:00 then when I refresh on 02/10/2020 I would have three columns one showing the value as when we captured yesterday and then the 'real time' value?

[jthomson]

You're going to need to work out how you store more than one value for a given value, this is more a question of how you store your data rather than anything within PBI Desktop at this stage. Once you've got a data model in place that can store id-value-date for more than one date, your desired end result should be trivial, but you know your data better than I do, there's many ways to do this but only you'll know what the best way to solve the problem is.