Forum Discussion
Moving values to new dates
Trying to build a model to guess when job would be quoted based on a table which contains data for job names and possible award dates.
I would like to start very simple by plotting a chart with the same amounts however placed 45 days prior to the award date.
Can I simply do it via a measure or do I really need to create another table in the query?
Thanks.
I think you need to have a datetime dimension table. If you don't have, you need to create one first.
You can set up the date scope of that table.
Then you build relationship with your fact table. I think in the real data the relationship here should be one to many
At last, you can use DAX to get the date 45 days ago.
Hope this is helpful.
Thanks and BR
Ryan
6 Replies
Welcome to Power BI Community.
Please share sample data and screenshots of the expected results :)
OmegaHi, I'm still trying to figure out on how to attach files to this chat.
Inserted a pic of what I am trying to do in POWER BI.
Column A is a date from the system and Column B is a calculated one (Column A - 45). And I'd to link values from column C to column B.
I understand I could have it done at query level however I would like to know if a measure with DAX could do the trick dynamically.
I think you need to have a datetime dimension table. If you don't have, you need to create one first.
You can set up the date scope of that table.
Then you build relationship with your fact table. I think in the real data the relationship here should be one to many
At last, you can use DAX to get the date 45 days ago.
Hope this is helpful.
Thanks and BR
Ryan
Thanks ryan_mayu for your simple solution and detailed explanation. t by creating a new column in my Data Model. Where I was not going anywhere was because I was trying to get this done by just one DAX formula and only virtual tables. Maybe I need to use things like CALCULATETABLE and VARIABLES to get it done.
nonetheless, appreciate your input and have already implemented it to my model. Again, thanks a lot
Hi RodBaylon
Your question is not very much clear, it seems like you are looking for something that can be acheived with the DATEADD function.
To get your question answered quick please see this post.
Regards
Affan
Hi affan, thanks for the feedback. A summary of what I'm trying to do:
Column A is a date from the system and Column B is a calculated one (Column A - 45). And I'd to link values from column C to column B.
I understand I could have it done at query level however I would like to know if a measure with DAX could do the trick dynamically.
I tried DATEADD however it was not moving values (Column C) from dates in column A and associating to dates in Column B. Unless I am doing something wrong. DATEADD was looking for a past reference and because there is none, my next chart was empty.
thanks for the link, I'm going to check it out.
