MAXX(DATESINPERIOD() not producing correct date range when a date filter is applied.

[Anonymous]

Hi All, 

I am trying to create a DAX measure that: 

1. Calculates the twelve month rolling sum of total income per month. [Twelve Month Rolling Income] 

Twelve month income = CALCULATE(SUM(['table'[total_fees]), DATESINPERIOD('Date'[date], MAX('Date'[date]), -12, MONTH))

2. Finds the maximum [Twelve Month Rolling Income] over the past 12 months. [Highest Twelve Month Rolling Income]

Highest twelve month rolling income = MAXX(DATESINPERIOD('Date'[startofmonth], max('Date'[date]), -12, MONTH), [Twelve Month Income])

I have managed to create the DAX measures for this and everything works fine until I add a date slicer or date filter onto the visual. Once a date slicer is added to the visual, [Highest twelve month rolling income] measure produces a local maximum value within the date slicer range rather than the maximum across the 12 months. 

The total table produced is: 

startofmonth	twelve month income	highest twelve month income
01/01/2021	2083	2083
01/02/2021	2060	2083
01/03/2021	2258	2258
01/04/2021	2083	2258
01/05/2021	2998	2998
01/06/2021	1515	2298
01/07/2021	2784	2298

When the a date slicer is applied (01/04/2021 to 01/07/2021), the [highest twelve month rolling] measure produces this "2083" for , 01/04/2021. This is the local maximum across twelve months from 01/04/2021 restricted to the date slicer (01/04/2021 to 01/07/2021). This is not I want because it doesn't capture twelve month income values earlier than 01/04/2021 to produce the maximum value across twelve months from 01/04/2021.  

startofmonth	twelve month income	highest twelve month income
01/04/2021	2083	2083
01/05/2021	2998	2998
01/06/2021	1515	2298
01/07/2021	2784	2298

This is what I WANT the table to produce: "2058" for 01/04/2021, which is the highest twelve month income value out of the last twelve months from 01/04/2021.

startofmonth	twelve month income	highest twelve month income
01/04/2021	2083	2258
01/05/2021	2998	2998
01/06/2021	1515	2298
01/07/2021	2784	2298

Please help me with this issue! 

Thank you! 

[Anonymous · Accepted solution]

I have managed to find the solution: 

Change the dax measure to: 
var maxdate = max('Date'[date])

var results = calculate(maxx(DATESINPERIOD('Date'[startofmonth], max('Date'[date]), -12, MONTH), [Twelve Month Income]), all('Date'),'ppdw DimDate'[date] <= maxdate)

return results

[v-rongtiep-msft]

Hi @Anonymous ,

I have create a simple sample, please refer to it to see if it helps you.

Create measures.

_now =
CALCULATE (
    SUM ( 'Table 1'[value] ),
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[year]
            = SELECTEDVALUE ( 'Table 1'[year] ) - 1
            && 'Table 1'[month] = SELECTEDVALUE ( 'Table 1'[month] )
    )
)

maxvalue_ =
MAXX (
    FILTER (
        ALL ( 'Table 1' ),
        'Table 1'[year] = SELECTEDVALUE ( 'Table 1'[year] )
    ),
    [_now]
)

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Please get familiar with time-intel functions, what their requirements are and when they can't be used. It seems you're using them incorrectly. For instance, this:

DATESINPERIOD('Date'[startofmonth], max('Date'[date]), -12, MONTH)

does not seem to be a correct use.

Please read this carefully: https://dax.guide/datesinperiod 

By the way, you should never use any other date column as the first argument but only the Date column in the Dates table. It has to be the column that has the full set of dates, day by day. No "startofmonth" please.