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

[aabati]

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

[v-ljerr-msft]

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

[aabati]

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

[v-ljerr-msft]

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

[aabati]

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

[v-ljerr-msft · Accepted solution]

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] )
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