Calculation error in calculation group. Return last 12 months

[Rai_Lomarques]

I am creating a chart in Power BI that needs to show the highest Sellout value in a constant straight line on the Y axis. The X axis should represent months in "MM/YYYY" format, and the display should be limited to the last 12 months prior to the month filtered by the user, using the calculation group I already have implemented, called Last 12M. However, the Max Sellout measure, which calculates the highest Sellout value in the chart, currently does not respect the calculation group filter and shows all months in the period.

My question is: How can I adjust the Max Sellout measure so that it respects the last 12 months limitation when used in the chart, considering that the Last 12M calculation group is active, so that the Max Sellout line in the chart represents the highest Sellout value within the range of 12 months prior to the month selected in the slicer?

The measurements are below:

Max Sellout =
    MAXX(
        ALL('dCalendar (Aux)'[YEAR/MONTH],'dCalendar (Aux)'[IndexYearMonth]),
        [Sellout]
    )

The measure below is a calculation group and I am using it as a filter to show only the last 12 months prior to the year/month being filtered.

Last 12M = 
VAR DateReference = MAX( 'dCalendário'[Date] )
VAR PreviousDate = 
    DATESINPERIOD(
        'dCalendar (Aux)'[Date],
        DateReference ,
        -12,
        MONTH
    )
VAR Resul =
    CALCULATE(
        SELECTEDMEASURE(),
        REMOVEFILTERS( 'dCalendar' ),
        KEEPFILTERS( PreviousDate ),
        USERELATIONSHIP( 'dCalendar'[Date], 'dCalendar (Aux)'[Date] )
    )
RETURN
Resul

See the chart above. The columns are correctly returning only the values ​​of the last 12 months. It is a simple measure SUM(fSales_Sellout[Amount]). However, the measure 'Max Sellout' is returning all months. I need the DAX measure to return values ​​only for the months that the calculation group is filtering on.

[v-kathullac]

Hi @Rai_Lomarques   ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @Rai_Lomarques   ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[Deku]

Since you are calling 

ALL('dCalendar (Aux)'[YEAR/MONTH],'dCalendar (Aux)'[IndexYearMonth])

 within the measure you are overwriting any external filters on date.

In the measure below we are still calculating the maxSellout but we are only returning the result if it falls within the 12 month period. I think this should be ok, even with the effect of the calc group on it

VAR DateReference = MAX( 'dCalendário'[Date] )
VAR PreviousDate = 
    DATESINPERIOD(
        'dCalendar (Aux)'[Date],
        DateReference ,
        -12,
        MONTH
    )
VAR maxSellout =
   MAXX(
        ALL('dCalendar (Aux)'[YEAR/MONTH],'dCalendar (Aux)'[IndexYearMonth]),
        [Sellout]
    )
RETURN
IF( MAX( 'dCalendar (Aux)'[Date] ) in PreviousDate, maxSellout )

[Rai_Lomarques]

Well done, tank you!