Trouble modelling columns names that are related to dates.

[Rag_G]

Hi everybody!

I have data from Sage50 for nominal codes. I am trying to create a rolling P & L statement. The problem is there are no dates in the table. I only have column headers (as above) going back from current year to prior year 5. 

I need to find a way to structure this data so that i have dates related to these column headers (e.g. Dec'19 back to Jan'14) and so that when year end is complete and data refreshed, power bi will know the current year months (e.g. "BALANCE_MTH1") will be the current year, Jan'20 - Dec'20 for example or Jan'21 - Dec'21 etc.

Is this possible?

[dax]

Hi @Rag_G  

You could try to choose all columns, then click unpivot column, then you could use replace or conditional column to replace existing value.  In addition, if possible, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rag_G]

Hi Zoe,

Thanks for the reply.

See above example of prior year sales balances for each month under nominal code 4000.

What i am trying to achieve is to replace the column titles with a relevant month/year so that i can use them for time comparisons, etc. I assumed this might be an unpivot job but then how can i replace and relate each month/year correctly?

An issue i am thinking that might occur with this when the end of year is run and the data moves forward is that "current year month 1" will change and relate to 2020 rather than 2019 and "Prior year month 1" will be 2019, so on. The original database column headers will be in the original format. so If i have told power bi that "current year month 1" is Jan'19, when the data has moved forward to Jan'20 it will be wrong if i just replace the values. Is there a condition to tell power bi the "current year" title data will always be current year and each prior year to that will work backwards from the current.

I hope what i have written here makes sense!

Cheers,
Ross

[Rag_G]

Further to my last reply i have been playing around trying to work it out.

This is as far as i have got. I have extracted the month numbers which i can use to relate to months in my date table. However i am still struggling to relate "PRIOR_YR" "PRIOR_YR2" etc to the year it relates to realtive to the current year. 

The current year will now have the word "BALANCE"and prior years as above due to me splitting by delimiter and then again by number of characters (See below). I have tried to research m code, DAX or anything i can use to basically say if Attribute.1 is "BALANCE" then return current year but no joy yet. Once i have this i can hopefully use that to work backwards to produce the correct prior year date.
I am quite new to all of this but i assume there is a function i can use that can return prior year, prior year +1 based on current year and so on...?

Am i going horribly wrong here or is there method to my madness?

Cheers,
Ross.

[amitchandak]

Is it Import or Direct Query?

[Rag_G]

Import 🙂