Moving values to new dates

[RodBaylon]

Hi there, 1st time posting a question so I apologize in advance for any eventual mistake.
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.

[ryan_mayu · Accepted solution]

@RodBaylon 

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

[RodBaylon]

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

[affan]

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

[RodBaylon]

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.

[Omega]

Welcome to Power BI Community. 

Please share sample data and screenshots of the expected results 🙂

[RodBaylon]

@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.