Get the Max Available Month on Selection

[vin26]

Hello,

I am trying to calculate average of value for last 6 months by skipping selected month. When I select a specific month if the data exist for the specific employee in the selected month I am getting correct numbers. But the issue is, if the selected month does not have any value, the measure is not picking up previous month numbers. Please help me to get the Max available month for the specific Employee and calculation should be based on available numbers of months during those selected 6 months. Eg: if I select Aug’24, Avg would be Feb to July or any available data between these time period. Here is my data and measures:

Month	EMP ID	Amount
2024-01	A100	4657
2024-02	A100	3480
2024-03	A100	4965
2024-04	A100	1510
2024-05	A100	4384
2024-06	A100	1374
2024-01	A200	3369
2024-02	A200	2445
2024-03	A200	4494
2024-04	A200	3621
2024-05	A200	2093
2024-06	A200	1350
2024-01	A300	3447
2024-02	A300	3603
2024-03	A300	3010
2024-04	A300	2305
2024-01	A400	1470
2024-02	A400	4998
2024-03	A400	2158

Data Modeling

Measure:

Measure = CALCULATE(Average(Table1[Amount]),DATESINPERIOD('DimDate'[Month],EOMONTH(MAX('DimDate'[Month]),-1),-6,MONTH))

Max Month

Measure 2 = EOMONTH(MAX(Table1[Month]),-1)

[rajendraongole1 · Accepted solution]

Hi @vin26 -Create a Measure to Get the Max Available Month for Each Employee

Max Available Month =
CALCULATE(
MAX(Table1[Month]),
FILTER(
ALL(Table1),
Table1[EMP ID] = MAX(Table1[EMP ID]) && Table1[Month] <= MAX('DimDate'[Month])
)
)

Measure for last 6 months calc.

Average Last 6 Months =
CALCULATE(
AVERAGE(Table1[Amount]),
DATESINPERIOD(
'DimDate'[Month],
[Max Available Month],
-6,
MONTH
),
Table1[Amount] <> BLANK()
)

check with above measures, if still issue exist please share the pbix file by removing the sensitive data.

[vin26]

@v-zhengdxu-msft @rajendraongole1 Thanks a lot for your responses. Both the solution works and my logic also working. Actual issue was with my Data modelling, I had used  'Both' in cross filter direction in connection between DimDate and Table1, after making the connection to single worked fine. Thanks again

[v-zhengdxu-msft · Accepted solution]

Hi @vin26 

Please try this:
Maybe you can delete the relationship between the Table1 and the DimDate:

Then add a measure:

MEASURE =
VAR _slicer =
    MONTH ( MAX ( 'DimDate'[Month] ) )
RETURN
    CALCULATE (
        AVERAGE ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( 'Table'[Month] ) < _slicer
                && MONTH ( 'Table'[Month] ) >= _slicer - 6
        )
    )

Then add a slicer with DimDate[Month]:

The result is as follow:

Best Regards

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

[rajendraongole1 · Accepted solution]

Hi @vin26 -Create a Measure to Get the Max Available Month for Each Employee

Max Available Month =
CALCULATE(
MAX(Table1[Month]),
FILTER(
ALL(Table1),
Table1[EMP ID] = MAX(Table1[EMP ID]) && Table1[Month] <= MAX('DimDate'[Month])
)
)

Measure for last 6 months calc.

Average Last 6 Months =
CALCULATE(
AVERAGE(Table1[Amount]),
DATESINPERIOD(
'DimDate'[Month],
[Max Available Month],
-6,
MONTH
),
Table1[Amount] <> BLANK()
)

check with above measures, if still issue exist please share the pbix file by removing the sensitive data.