Calculate function with a filter based on another measures's value- fiscal month

[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).

Now I need to create formulars for the fiscal LY and YTD.

Firstly, what I did is I added a Fiscal month Name, Year and Monthyear column to each date in the calendar table.

Then I created a Measure [Fiscal Month Year Num Returned], that returns a month and a previous year based on selected month from the slicer. In my example, the user selects FiscalMonthYear: "Apr-18", and the measure returns the value "42017" (which is month 4 plus year 2018 minus 1). I want to use this measure tocalculate a value for the same fiscal month of the last year.

Here is what I got so far:

Revenue Prior Year = CALCULATE([Revenue],FILTER(ALL('Calendar'),'Calendar'[FiscalMonthYearNum]=VALUE('Calendar'[Fiscal Month Year Num Returned]))), where FiscalMonthYearNum is the column from the query  and [Fiscal Month Year Num Returned] is the measure that returns the value 42017. I want the table to sum Revenue only for the dates that are marked "42017" in the column FiscalMonthYearNum.

However this function does not work. Any idea how to fix it? I tried a lot of different functions but did not make it work..:(

Also, any suggestions on how to create a YTD formula for my scenario?

[v-lili6-msft]

Hi@ bayes

You can try to use SAMEPERIODLASTYEAR function and DATESBETWEEN function like below:

Measure 3 = CALCULATE(SUM(Table2[Qty]),DATESBETWEEN('Calendar'[Date],SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])),LASTDATE('Calendar'[Date])))

Result:

Best Regards,

Lin

[bayes]

Unfortunately, this formula returns incorrect amounts.. Last period should somehow be controlled by the FiscalYearMonth column

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

[v-lili6-msft]

Hi@ bayes

After my research, you may use var function in your measure like below:

Revenue Prior Year = 
VAR Fiscal=[Fiscal Month Year Num Returned]  RETURN
CALCULATE (
    SUM(Table2[Qty]),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Fiscal almonthyearnum]
            = Fiscal
    )
)

For example

Basic data

Result:

Best Regards,

Lin

[bayes]

Hi Lin, that worked! You are a genius 🙂 However, I noticed that using a [Fiscal Month Year Num Returned] measure has it's limitations: I cannot have multiple month selection and I can't use it to return values when the Fiscal MonthYear is used as a row or a column not a slicer. Do you know what solution will work to return Revenue Prior Year based on a column or row. For example, if I needed to display Revenue Prior Year as a column next to Revenue from the screenshot below:

Thanks in advance,

Katya