Auto Adjusting Dates In Model

[SMW]

Hello, 

I have a bit of a challenge. I have created a PowerBi model that has many fact and dimenstion tables. Currently the data source is an excel file with static dates. I have created the dashboard to be a demo of sorts that uses time intelligence and therefore uses many of the date columns throughout the data model analyzing this week over last week etc. Since the source document is static, What I would like to create is an automatic way to update the dates throughout PBI to remain "current" so these function remain in effect without having to manually go in and update the dates in the excel file. For example, if I could automatically add a month to all the dates being used in the model, it would remain relevant anytime the dashboard is viewed.

I tried a solution to create a calculated column in the date table (which was not created in PowerQuery) in order to connect relationships using that. However this failed and resulted in many to many relationships. 

Adjusted Date = DATEADD('Date Table'[Date],1,YEAR)

Any suggestions or advice would be greatly appreciated. Is this even possible to acheive? 

Thanks

[SMW]

I am not sure where to add the calculated column. Is this for every table and every date column in the data? That is over 20 date columns that are currently all connected to a single date table. I am not sure what source of the data I need to read in otherwise. There are many different tabs within the excel all serving as various tables then some additional tables created in powerquery for dimensions. Wouldn't adding one month to the static date stagnate at the next month? Iam needing this to be contiuously adding a month to the original date. So that if the original date was 1/1/2020 if I looked at it in 3 months the new adjusted date would be 3/1/2020 and so on.

Is there a way to create automatic updates on all the date columns across the entire model without having to create calculated columns for each connection within each table? 

[Ghhousuddin]

If you want to automatically adjust the dates in your Power BI model without having to manually update the source Excel file, you can use a combination of Power Query and DAX.

Here's how you can do it:

1. In Power Query, create a new query that reads the source Excel file and adds a new column with the adjusted dates. You can use the `Date.AddMonths` function to add a fixed number of months to each date in your source data.

```
Adjusted Date = Date.AddMonths([Date], 1)
```

2. Load the modified data into the Power BI model and create relationships between the date column in the source data and the adjusted date column in the new query.
3. In your DAX calculations, replace references to the original date column with references to the adjusted date column.

For example, if you have a measure that calculates sales for the current month using the original date column, like this:

```
Sales Current Month = CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Date], DATE(YEAR(TODAY()), MONTH(TODAY()), 1), TODAY()))
```

You can update it to use the adjusted date column instead, like this:

```
Sales Current Month = CALCULATE(SUM(Sales[Amount]), DATESBETWEEN(Sales[Adjusted Date], DATE(YEAR(TODAY()), MONTH(TODAY()), 1), TODAY()))
```

This will ensure that the measure always calculates sales for the current month based on the adjusted dates, regardless of whether the source Excel file has been updated.

4. Optionally, you can hide the original date column in your visualizations to avoid confusion.

To do this, go to the "Fields" pane, find the original date column, click on the ellipsis button next to it, and choose "Hide in report view".

With these steps, your Power BI model should automatically adjust the dates in your data based on the formula you specified in Power Query, allowing your time intelligence calculations to remain relevant without having to manually update the source Excel file.