Forum Discussion
Helper IIRolling 12 months with Dynamic target
Hello,
I have a DAX code which was suggested in topic
https://community.fabric.microsoft.com/t5/Desktop/Dynamic-Targets-based-on-filters-or-no-filters/m-p/3437996#M1140453
Scores Target =
VAR Plant_target = 0.9
VAR Corporate_target = MAX('Cust Satisfaction'[Target])
VAR Filtered = ISFILTERED('Cust Satisfaction')
RETURN
IF (
Filtered,
IF (
HASONEVALUE('Master Corporation'[Corporation]), -- Replace with your actual filter column
Corporate_target, -- Specific target when a filter is applied
Plant_target -- No specific target when multiple filter values are selected
),
Plant_target
)-- Default target when no filters are applied
I would adopt this code to 12month rolling as below:
Score Target =
VAR MaxDate =
MAX ( 'DATE_DUP'[WC_Month] )
VAR MinDate =
DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) )
RETURN
CALCULATE (
[Scores Target],
FILTER ( 'COQ Calendar', 'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate )
)
Anyway output is wrong becasue target is visible through all years like on below screen:
When I change "Plant_target" to Blank() as below:
Scores Target =
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
),
Plant_target
)-- Default target when no filters are applied
Then 12 months rolling data are visible but target is unvisible when slicer is unfiltered but I need to see Plant target = 90%
Can you please help me becasue I don't know where is problem even if tried to copy the same DAX code to different places ? Once I filter data by slicer then everything is OK and I can see 12 months data.
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.
2 Replies
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - 123abc
Community Champion
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.
