Import Data valid for all years

[Mangnuel]

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

[bhanu_gautam]

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

[Mangnuel]

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 work

[v-sgandrathi · Accepted solution]

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.

[v-sgandrathi]

Hi @Mangnuel,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thank you.

[v-sgandrathi]

Hi @Mangnuel,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[Mangnuel]

yes it works, wanted to check the system for a few days.
All the measures, filters and tables work.

Thanks a lot