Forum Discussion
Rolling 12 months with Dynamic target
- 3 years ago
I see that you are trying to implement a dynamic target with a 12-month rolling period, but you're facing issues with the visibility of the target. To make sure the target is calculated correctly for the 12-month rolling period, you should adjust your DAX code. The problem you are encountering might be because you are calculating the target independently of the rolling period. Here's a modified DAX formula that combines both the dynamic target and the 12-month rolling period:
Score Target =
VAR MaxDate = MAX('DATE_DUP'[WC_Month])
VAR MinDate = DATE(YEAR(MaxDate) - 1, MONTH(MaxDate), DAY(MaxDate))VAR Plant_target = 0.9
VAR Corporate_target = MAX('Cust Satisfaction'[Target])
VAR Filtered = ISFILTERED('Master Corporation'[Corporation])RETURN
IF (
Filtered,
IF (
HASONEVALUE('Master Corporation'[Corporation]), -- Replace with your actual filter column
Corporate_target, -- Specific target when a filter is applied
BLANK() -- No specific target when multiple filter values are selected
),
IF (
COUNTROWS(FILTER('COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate)) > 0,
Corporate_target, -- Show the Corporate target for the selected 12-month rolling period
Plant_target -- Show the Plant target for the entire 12-month rolling period
)
)In this modified DAX formula, we first calculate the 12-month rolling period using the MinDate and MaxDate variables. Then, we incorporate the dynamic target logic you provided, which checks for slicer filters and whether a specific filter is applied. The Plant target is displayed when no filters are applied, and the Corporate target is displayed for the selected 12-month rolling period or when slicer filters are used.
This should give you the desired result where the target is visible for the 12-month rolling period, and the Plant target is displayed when no slicer filters are applied.
I see that you are trying to implement a dynamic target with a 12-month rolling period, but you're facing issues with the visibility of the target. To make sure the target is calculated correctly for the 12-month rolling period, you should adjust your DAX code. The problem you are encountering might be because you are calculating the target independently of the rolling period. Here's a modified DAX formula that combines both the dynamic target and the 12-month rolling period:
Score Target =
VAR MaxDate = MAX('DATE_DUP'[WC_Month])
VAR MinDate = DATE(YEAR(MaxDate) - 1, MONTH(MaxDate), DAY(MaxDate))
VAR Plant_target = 0.9
VAR Corporate_target = MAX('Cust Satisfaction'[Target])
VAR Filtered = ISFILTERED('Master Corporation'[Corporation])
RETURN
IF (
Filtered,
IF (
HASONEVALUE('Master Corporation'[Corporation]), -- Replace with your actual filter column
Corporate_target, -- Specific target when a filter is applied
BLANK() -- No specific target when multiple filter values are selected
),
IF (
COUNTROWS(FILTER('COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate)) > 0,
Corporate_target, -- Show the Corporate target for the selected 12-month rolling period
Plant_target -- Show the Plant target for the entire 12-month rolling period
)
)
In this modified DAX formula, we first calculate the 12-month rolling period using the MinDate and MaxDate variables. Then, we incorporate the dynamic target logic you provided, which checks for slicer filters and whether a specific filter is applied. The Plant target is displayed when no filters are applied, and the Corporate target is displayed for the selected 12-month rolling period or when slicer filters are used.
This should give you the desired result where the target is visible for the 12-month rolling period, and the Plant target is displayed when no slicer filters are applied.