Fiscal Year -> Calendar Year


#1

Hi Everyone & Sam,

I am currently trying to create a profit and loss report similar to the one you have created Sam. I am currently connected to a database that in history (prior to 2018) has run based on a fiscal year calendar however the company has currently been restructured in 2018 to run on a calendar year and as a result the period column is not consistent and this is a problem when i am wanting to compare to history. Do you have a best solution/ protocol to adjust the history in order for it to be compared with current calendar year e.g adjusting date table vs measures/ calculations.

e.g -
image

Cheers,


#2

Are you looking to compare the same time period every year? or do you need to compare one time period as financial years and one as calendar years?

If so, that’s quite unique.

You would have to create a few unique DAX measures I think.

Can you example one example of how you want to showcase this in a visual? I need to understand a bit more on that.

Maybe some image if you have them?


#3

Hi Sam,

i will show you the current reporting solution/ format in excel that they have in which i am attempting to replace as an example.

as you can see here in 2018 (running calendar year) it has data from january to current date.

However if we look back in history (2017) it will only display half of the year due to previous years running FY

This is an example of what the fact table contains (there is no month column i am just putting it there to help show how there period numbers are represented)

Therefore what i am trying to achieve is if i am looking at 2018 period 1 = January, i wish to compare this against LY 2017 january = period 7. I was just wondering what the best method is or if this is even correct to do.

Let me know if you need more information

Cheers Sam


#4

Ok thanks for these.

I think this actually shouldn’t be to difficult I don’t think. Maybe I’m wrong but if you’re now just comparing like for like on a calendar basis then you can just use simple time intelligence formula couldn’t you?

Like DATEADD

You definitely just want to keep a regular date table. I don’t think you need to change anything or customize it in any way.

You should just be able to use the natural context around years and then time intelligence calculations.

Maybe I’m under thinking it here, but it doesn’t seem like anything unique really needs to be done here.

Let me know what you think.