Fiscal months with irregular intervals

[bayes]

Hello, our company is using fiscal months. The length of these periods are set up randomly without any kind of logic. This is how the past year looks like: 5-4-4-4-5-4-4-5-4-5-4-4 weeks in each month (hence, no option to write a logic function).

I need to create a formula similar to SAMEPERIODLASTYEAR:

Revenue Prior Year=CALCULATE([Revenue],SAMEPERIODLASTYEAR('Calendar'[Date])). The built-in formular does not work as my fiscal periods do not match.

I added columns Fiscal month Name , Year, MonthYearNum and FIscalMonthYear to each date in the calendar table to determine which fiscal period each date belongs to.

 

What I'm trying to achieve is to return the revenue amount for the same fiscal month of the prior year based on the column  FiscalMonthYear. For example, I need to display the measure Revenue Prior Year as a column next to Revenue from the screenshot below.  Could someone please help? I'm trying to solve this issue for 5 days now with no luck.

[v-shex-msft]

HI @bayes,

Normal fiscal calendar only calculate to month leave, it will ignore next date level for calculate.

I'm not clear for your fiscal calendar, it seems too accurately so that hard to calculate. (fiscal month correspond to real calendar date, but it not care about detail days of each month)

In my opinion, you can consider to manually calculate out previous fiscal date, then use this date range to find out related real calendar date. 

Sample measure formula:

LFYTD =
VAR realDate =
    CALCULATE ( VALUES ( Table[Date] ), ALLSELECTED ( Table[Fiscal Date] ) )
VAR _min_fiscal =
    LOOKUPVALUE ( Table[Fiscal Date], Table[Date], MINX ( realDate, [Date] ) )
VAR _max_fiscal =
    LOOKUPVALUE ( Table[Fiscal Date], Table[Date], MAXX ( realDate, [Date] ) )
VAR _prev_min_fiscal =
    PATHITEM ( SUBSTITUTE ( _min_fiscal, "-", "|" ), 1 ) & "-"
        & VALUE ( RIGHT ( _min_fiscal, 2 ) ) - 1
VAR _prev_max_fiscal =
    PATHITEM ( SUBSTITUTE ( _max_fiscal, "-", "|" ), 1 ) & "-"
        & VALUE ( RIGHT ( _max_fiscal, 2 ) ) - 1
VAR _prev_real_min =
    CALCULATE (
        MIN ( Table[Date] ),
        FILTER ( ALL ( Table ), Table[Fiscal Date] = _prev_min_fiscal )
    )
VAR _prev_real_max =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER ( ALL ( Table ), Table[Fiscal Date] = _prev_max_fiscal )
    )
RETURN
    CALCULATE (
        SUM ( Table[Revenue] ),
        FILTER ( ALL ( Table ), [Date] >= _prev_real_min && [Date] <= _prev_real_max )
    )

Regards,

Xiaoxin Sheng

[bayes · Accepted solution]

This formula below worked as a solution. Where FiscalMonthYearNum is month number+year (42018 for April 2018). And PreviousFiscalMonthYearNum = FiscalMonthYearNum-1 (42017 for April 2017)

Revenue Prior Year =
CALCULATE([Revenue],
FILTER (
ALL ( 'Calendar - new' ),
CONTAINS (
VALUES ( 'Calendar - new'[PreviousFiscalMonthYearNum] ),
'Calendar - new'[PreviousFiscalMonthYearNum],
'Calendar - new'[FiscalMonthYearNum]
)
)
)