Problem with Fiscal MTD Last Year

DAX DAX Calculations
1

I’m working with a fiscal calendar and a Fiscal Month/Year date slicer.

If the current month is selected, then MTD should show only up to yesterday, not the full month.
For last year MTD, the result should be through the same fiscal day of the month as yesterday, for the same fiscal month and fiscal year -1.

To determine whether to use the end of the month, or yesterday’s date, I created the following measure
EndDate = IF(MAX(‘Dates’[Date]) >= [DateYesterday],[DateYesterday],MAX(‘Dates’[Date]))

Then, I am getting the fiscal day of the month for this date with the measure below:
EndDateFiscalDayofMonth = Calculate(MAX(Dates[FiscalDayoftheMonth]),Filter(Dates,Dates[Date]=[EndDate]))

This works fine for the current year, but does not work for last fiscal year. I am getting the entire period. Measure that does not work is below. If I hardcode a value for [EndDateFiscalDayofMonth], then it does work.

SalesAmt Last Fiscal Year MTD =
var _selectedFiscalPeriod= SELECTEDVALUE(Dates[FiscalPeriod],0)
var _selectedFiscalYear= SELECTEDVALUE(Dates[FiscalYearOnly],0)-1
Return
CALCULATE(SUM(Sales[Sales Amount]),
Filter(All(Dates),Dates[FiscalPeriod]= _selectedFiscalPeriod && Dates[FiscalYearOnly]=_selectedFiscalYear
&& Dates[FiscalDayoftheMonth] <= [EndDateFiscalDayofMonth]))

Below is a test .pbix. The results in green are what I want, but not by hardcoding a number
Date Testing v4.pbix (1014.5 KB)

Thanks for your help!!

2

Hi @DeanJ,

Give this a go

SalesAmt Last Fiscal Year MTD v2 = 
VAR _FiscalYear= MAX( Dates[FiscalYearOnly] )-1
VAR _FiscalPeriod= MAX( Dates[FiscalPeriod] )
VAR _FiscDayOfMonth = CALCULATE( MAX( Dates[FiscalDayoftheMonth] ), FILTER( ALL( Dates ), Dates[Date] = MAX( DateYesterday[DateYesterday] )))
RETURN

CALCULATE( SUM( Sales[Sales Amount]),
    FILTER( All(Dates),
        Dates[FiscalYearOnly] = _FiscalYear &&
        Dates[FiscalPeriod] = _FiscalPeriod &&
        Dates[FiscalDayoftheMonth] <= _FiscDayOfMonth
    )
)

I hope this is helpful.

3 Likes
3

Thanks @Melissa !! That works. Do you know why the other approach did not work? Must be the filter context applied to EndDate measure. I will have many MTD measures like this and was trying to be efficient by using another measure instead of repeating the code in each one.

Thanks again!

4

Hi @DeanJ,

You are evaluating the [EndDateFiscalDayofMonth] measure in the filter context of each row in your table/matrix and you need to place is outside that context like you did for Fiscal Period and Year.

I hope this is helpful.

1 Like
5

Perfect- thank you!!