Fiscal Year -> Calendar Year

Hi Everyone,

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,

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?

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

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.

I am new to DAX and recently learned Time Intelligence functions do not work if you are using a fiscal calendar that does not map to the standard calendar (disappointing given how commonly this happens). I would like to show a demo of Power BI using our data but need to first get the date table and related time-based measures configured correctly.

Using some of the Enterprise DNA videos on custom calendars, I started creating a custom date table but got twisted around when trying to handle leap-years and fiscal week numbering. Then I became more confused thinking about how to CALCULATE( ) my way to measures for things like YoY, YoY by Qtr, QoQ, MoM, MoM by Yr, etc.

I’m sure this will become easier the more I get to know DAX. What do you recommend for learning the DAX code necessary to manually create equivalent Time Intelligence functions (for use with our fiscal calendar)?

Best!

They definitely do work to a fiscal calendar (just not a non standard calendar like 445 calendars). Is this what you have or are you just talking financial years?

Setting up financial years within your date tables is easy and very doable.

You shouldn’t have to change anything around the standard date calendar.

Have you check out the resources available here. You can just copy the date tables here if you truly require the non standard version.

Appreciate the response, Sam! Apologies, I should have been more specific… we use a 445 calendar.

Ok sure thing.

I guess my advice toto really dive into combinations of CALCULATE and FILTER. These are the two key formulas to create time intelligence with custom calendars.

All you need will be some derivative of the examples shared above that are located in the time intelligence calculations mini series module.

Chrs
Sam

1 Like

Sounds good, I will check it out!

Best,
Garth

Hi Sam,

As this s my first forum post, by way of introduction my name is Adam Volcov and I am relatively new to Power BI. I run my own accounting business in Far North Queensland (Cairns) and am looking to use PBI to help my small business clients improve the management of their businesses.

As Australia always reports from July to June my question for this thread is whether it is possible to update the M Code in the Date Table to create the fiscal month and quarter columns? If so how would I do this? If this is not possible or desireable , would these columns need to be created each time a date table is added to a new client report or is there another way to “save” this date table for re-use with different client reports?

Appreciate a response and direction to other resources if this has already been asked and answered.

Many thanks

Adam

@VBA,

Welcome to the forum – great to have you here!

If you use the Extended Date Table code created by @Melissa, it automatically creates the FY and FM columns for you, based on an initial parameter you supply indicating what month your fiscal year starts in:

She and I have actually done an entire time intelligence video series on how to fully leverage the power of this extended date table.

I hope this is helpful.

  • Brian
1 Like

Hi Brian, thanks very much! Thats great.

I’ll take a look and the video too.

Regards

Adam

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Groups. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!