adding column dynamically depending on date || Complex problem

Hi omarelmb123 ,

While jennratten has produced your requested output perfectly, I would argue that what you're asking for is not, in fact, dynamic at all as you're having to hardcode dates and column names for each year required. Additionally, you're creating a denormalised table structure that is not the most efficient for reporting and may cause you issues later when trying to calculate measures.

I would suggest adding a custom column like this instead:

LevelOfSales_EOY =
if Date.Month([Date]) = 12 and Date.Day([Date]) = 31 then [Level of sales]
else null

The year for which you are viewing this new column value would then be controlled using a related calendar table, as is best practice.

Once this method is set up, it will dynamically add new entries to the column as new data values are added to the fact table with no further coding required.

Pete