- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-02-2017 12:11 PM

Hello !

Hope someone can help me here.

I found some articles about how to create a custom calendar (typical retailer fiscal calendar for instance) but it is unclear once you have created the calendar how I can calculate YTD, PTD etc..

My Fiscal calendar is a tyipical 4-5-4 where each week goes from Sunday to Saturday.

Sof ro instalce FY 2017 starts 5th of Feb 2017.

Thank you very much for your help

Antonio

Solved! Go to Solution.

Accepted Solutions

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-20-2017 02:12 AM

Hi @aabati,

Could you try the formula below to see if it works?

Start Day of Fiscal Year= VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VAR currentWeek = CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_YEAR] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ), 'Fiscal Calendar'[CAL_YEAR] = currentFiscalYear ) )

YTD Sales = VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) RETURN CALCULATE ( [Total Sales], FILTER ( 'Fiscal Calendar', 'Fiscal Calendar'[CAL_DAY] >= [Start Day of Fiscal Year] && 'Fiscal Calendar'[CAL_DAY] <= currentDay ) )

Also, for the YTD all I need is the "

Start Day of Fiscal Year", but what about the WTD and PTD ?

That should be similar. You just need "**Start Day of Week**" and "**Start Day of Period**" instead.

Start Day of Week= VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VARcurrentWeek= CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_WEEK] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ),'Fiscal Calendar'[CAL_WEEK] = currentWeek) )

Start Day of Period = VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VARcurrentPeriod= CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_PERIOD] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ),'Fiscal Calendar'[CAL_PERIOD] = currentPeriod) )

Regards

All Replies

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2017 10:35 PM

Hi @aabati,

but it is unclear once you have created the calendar how I can calculate YTD, PTD etc..

Have you managed to create your own Fiscal Calendar table? If so, could you post it here?

Have you attempted to write any formulas to calculate YTD? If so, where did it go wrong?

It's better to post your table structures with some sample data(even better to share a sample pbix file) and your expected result. So that we can better assist on the formulas to calculate YTD, etc in your scenario.

Regards

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-18-2017 05:39 AM

Hi,

Really sorry for the lack of details in my previous post.

I have created my Fiscal Calendar loading from an Excel file where I have 4 columns:

- CAL_DAY (es: 02-MAR-14)

- CAL_WEEK (es: 1501)

- CAL_PERIOD (es: 1501)

- CAL_YEAR (es: 2015)

I have no idea how I can write a formula to calculate YTD as my Fiscal year starts on a different date every year (usually from beginning of March, but not always the same date as week are from Sunday to Friday).

Thanks in advance for your help, I really love PowerBI !

Antonio

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2017 02:22 AM

Hi @aabati,

In your scenario, you can firstly create a new measure to get the **Start Day of current Fiscal Year**.

Start Day of Fiscal Year= VAR currentFiscalYear = CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_YEAR] ), 'Fiscal Calendar'[CAL_DAY] = MAX ( 'Fiscal Calendar'[CAL_DAY] ) ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ), 'Fiscal Calendar'[CAL_YEAR] = currentFiscalYear ) )

Then you should be able to use the formula below to calculate YTD.

YTD Sales = CALCULATE ( [Total Sales], FILTER ( 'Fiscal Calendar', 'Fiscal Calendar'[CAL_DAY] >= [Start Day of Fiscal Year] && 'Fiscal Calendar'[CAL_DAY] <= MAX ( 'Fiscal Calendar'[CAL_DAY] ) ) )

Regards

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-19-2017 08:48 AM

Thanks for your quick reply,

I tried to implement your solution but I got the following error:

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Also, for the YTD all I need is the "**Start Day of Fiscal Year** ", but what about the WTD and PTD ?

Thanks

Antonio

## Re: Setup a custom calendar and calculate YTD, PTD, etc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-20-2017 02:12 AM

Hi @aabati,

Could you try the formula below to see if it works?

Start Day of Fiscal Year= VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VAR currentWeek = CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_YEAR] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ), 'Fiscal Calendar'[CAL_YEAR] = currentFiscalYear ) )

YTD Sales = VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) RETURN CALCULATE ( [Total Sales], FILTER ( 'Fiscal Calendar', 'Fiscal Calendar'[CAL_DAY] >= [Start Day of Fiscal Year] && 'Fiscal Calendar'[CAL_DAY] <= currentDay ) )

Also, for the YTD all I need is the "

Start Day of Fiscal Year", but what about the WTD and PTD ?

That should be similar. You just need "**Start Day of Week**" and "**Start Day of Period**" instead.

Start Day of Week= VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VARcurrentWeek= CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_WEEK] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ),'Fiscal Calendar'[CAL_WEEK] = currentWeek) )

Start Day of Period = VAR currentDay = MAX ( 'Fiscal Calendar'[CAL_DAY] ) VARcurrentPeriod= CALCULATE ( MAX ( 'Fiscal Calendar'[CAL_PERIOD] ), 'Fiscal Calendar'[CAL_DAY] = currentDay ) RETURN CALCULATE ( MIN ( 'Fiscal Calendar'[CAL_DAY] ), FILTER ( ALL ( 'Fiscal Calendar' ),'Fiscal Calendar'[CAL_PERIOD] = currentPeriod) )

Regards