Reply
Highlighted
Frequent Visitor
Posts: 5
Registered: ‎01-11-2017
Accepted Solution

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

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


Accepted Solutions
Super Contributor
Posts: 2,678
Registered: ‎07-17-2016

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

Hi @aabati,

 

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

 

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] )
VAR currentWeek =
    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] )
VAR currentPeriod =
    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

View solution in original post


All Replies
Super Contributor
Posts: 2,678
Registered: ‎07-17-2016

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

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.Smiley Happy

 

Regards

Frequent Visitor
Posts: 5
Registered: ‎01-11-2017

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

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

Super Contributor
Posts: 2,678
Registered: ‎07-17-2016

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

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. Smiley Happy

 

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

Frequent Visitor
Posts: 5
Registered: ‎01-11-2017

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

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

Super Contributor
Posts: 2,678
Registered: ‎07-17-2016

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

Hi @aabati,

 

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

 

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] )
VAR currentWeek =
    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] )
VAR currentPeriod =
    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