Calculate a measure for custom date range

[Pinoo39]

Hey All

I'm trying to calculate a measure (e.g. Avg Sales) for the period of previous 11 month from the current selected month, here is what I wrote :

AVGSales Snapshot =

VAR CurrentMonth = SELECTEDVALUE(DimDate[FinMonthNumber])

VAR CurrentYear = SELECTEDVALUE(DimDate[FinYear])

VAR MaxMonthNum = CALCULATE(MAX(DimDate[FinMonthNumber]), ALL(DimDate))

RETURN

IF(HASONEVALUE(DimDate[FinMonthNumber]),

SUMMARIZE(

FILTER(ALL(DimDate),

(DimDate[FinMonthNumber] in VALUES(FILTER(DimDate, DimDate[FinMonthNumber] <= CurrentMonth)) && DimDate[FinYear] = CurrentYear - 1) &&

(DimDate[FinMonthNumber] > CurrentMonth && DimDate[FinYear] = CurrentYear)),

"AVGSales Snapshot", [Average Sales]),

BLANK())


Since I'm using Custom calendar (Financial Calendar) which is not matching with the normal calendar, Time Intelligence functions are not going to work.

Any Idea ?

[daxer-almighty · Accepted solution]

@Pinoo39 

I instructed you that you should create a column with consecutive numbering of fiscal months. I'm not talking about 1-12 but CONSECUTIVE numbers from 1 up to the last month you have in your calendar. Year has nothing to do with this.

[daxer-almighty]

// FiscalMonthID starts with 1 and goes up by 1
// until the very last fiscal month you have in
// the table.

AVGSales Snapshot =
var __minNumberOfMonths = 12
var __oneFullFiscalMonthVisible =
    var __oneMonthVisible = HASONEVALUE( DimDate[FiscalMonthID]
    var __dayCount = COUNTROWS( DimDate )
    var __fullMonthDayCount =
        CALCULATE(
            COUNTROWS( DimDate ),
            DimDate[FiscalMonthID] IN DISTINCT( DimDate[FiscalMonthID] ),
            ALL( DimDate )
        )
    return
        __oneMonthVisible
        &&
        ( __dayCount = _fullMonthDayCount )
        
var __thereIsEnoughMonthsToCalcOver =
    SELECTEDVALUE( DimDate[FiscalMonthID] ) >= __minNumberOfMonths
    
var __output =
    if( 
        __oneFullFiscalMonthVisible
        &&
        __thereIsEnoughMonthsToCalcOver,
        
        var __currentMonthID = SELECTEDVALUE( DimDate[FiscalMonthID] )
        var __lowerBound = __currentMonthID - __minNumberOfMonths + 1
        var __upperBound = __currentMonthID - 1
        return
            AVERAGEX(
                CALCULATETABLE(
                    Distinct( DimDate[FiscalMonthID] ),
                    DimDate[FiscalMonthID] >= __lowerBound,
                    DimDate[FiscalMonthID <= __upperBound,
                    all( DimDate )
                ),
                [Average Sales]
            )
    )
return
    __output

[Pinoo39]

Wow, Thanks for your help
would you please explain how it filters the year ?
If FiscalMonthID = 12 then the previous 12 month will be in the same year, but what if FiscalMonthID = 7 ?

[Pinoo39]

I do have a Month Number Column ("FinMonthNumber") starting from 1 as the beginning of Fincancial Year.
The question is how to select multiple months.  something like FinMonthNumber <= 6 will not work for Financial Months of 1 up to 6

[daxer-almighty]

Such a measure will be much simpler if you number your financial months consecutively starting with 1 (the very first month in your calendar). Why mess with years and months when you can use month numbers only? If you want to see how to correctly tackle financial calendars, you can go to Custom time-related calculations – DAX Patterns.

[Pinoo39]

No one Knows how to make it work ?