Forum Discussion
Import Data valid for all years
Hi PowerBi-World,
I need your help, again 😊
I have a lots of Data and everything is filter by either the Date or the Asset Code.
Goal ist to include a Target (expected Budget) for every month.
This table is valid for each year im working with the Data.
So how can I import the data and tell Powerbi, that it’s valid for every year.
Thanks a lot in Advance
Table/Data looks like this
 
and that's the calendar table
Hi Mangnuel.,
The issue with filtering happens because your Calendar table filters by full dates, but your ExtendedTargets table currently only links by Year and Month. Since your Calendar has a unique Date for each day, Power BI can’t filter the ExtendedTargets correctly on just Year and Month.
To fix this, you need to create a full date column in your ExtendedTargets table—typically the first day of each target month and then create a relationship between that date and the Calendar’s Date column.
Add this calculated column to your ExtendedTargets table:
TargetDate = DATE(ExtendedTargets[Year], ExtendedTargets[Month], 1)Then in the model view, create a relationship between:
ExtendedTargets[TargetDate] and Calendar[Date]
Make sure this relationship is active and single-directional from Calendar to ExtendedTargets.
This way, filtering by any date, month, or year on the Calendar table will correctly filter the targets as well.
I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.Best regards,
Sahasra.
6 Replies
Mangnuel Create a relationship between the Target table and your Calendar table. The relationship should be based on the month number.
To make the target valid for each year, you can create a calculated table that extends the target values for each year in your data.
You can use DAX to create this calculated table
DAX
ExtendedTargets =
CROSSJOIN (
DISTINCT ( 'Calendar'[Year] ),
'Target'
)Create a relationship between the ExtendedTargets table and your Calendar table based on both the Year and Month columns.
Now you can use the targets in your reports. For example, you can create a line chart that shows actual values versus target values over time.
Thanks bhanu_gautam ,
the created table looks o.k.
The filtering does not work.
Normally I have many different dates within the Datasheets, but the Calendar Tabel only has the date once.
No I have plenty of months and years to filter with.
Doesn't really workHi Mangnuel.,
The issue with filtering happens because your Calendar table filters by full dates, but your ExtendedTargets table currently only links by Year and Month. Since your Calendar has a unique Date for each day, Power BI can’t filter the ExtendedTargets correctly on just Year and Month.
To fix this, you need to create a full date column in your ExtendedTargets table—typically the first day of each target month and then create a relationship between that date and the Calendar’s Date column.
Add this calculated column to your ExtendedTargets table:
TargetDate = DATE(ExtendedTargets[Year], ExtendedTargets[Month], 1)Then in the model view, create a relationship between:
ExtendedTargets[TargetDate] and Calendar[Date]
Make sure this relationship is active and single-directional from Calendar to ExtendedTargets.
This way, filtering by any date, month, or year on the Calendar table will correctly filter the targets as well.
I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.Best regards,
Sahasra.
