Rolling 12 months with Dynamic target

[MKPartner]

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...

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. 

[123abc · Accepted solution]

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.

[lbendlin]

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-...
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/1447...