Forum Discussion
Resolver IGet the Max Available Month on Selection
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)
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.
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.
3 Replies
- rajendraongole1
Super User
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.
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.
vin26Anonymous 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