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.


#5

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!


#6

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.


#7

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


#8

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


#9

Sounds good, I will check it out!

Best,
Garth