Forum Discussion

MKPartner's avatar
MKPartner
Icon for Helper II rankHelper II
2 years ago
Solved

Rolling 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 =...
  • 123abc's avatar
    2 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.