Forum Discussion

Sruthychinnan's avatar
Sruthychinnan
New Member
2 years ago

Breaking the cumulative line

Cumulative line is breaking.How can I fix it. Below given is the code used for cumulative forecast, plan and actual

 




Cum_ProdQuantsBaseline =
VAR MAXPLAN = CALCULATE(MAX(ProdQuantsPivotPlan[Date]))
VAR BL_CPI = 362
VAR BL_DCI = 1018
VAR YrMth = FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]))
VAR RES = SELECTEDVALUE(Resource[Resource])
VAR Auth = LOOKUPVALUE('DataSet'[Author], 'DataSet'[CreatedPeriodIndex], BL_CPI, 'DataSet'[DC_Index], BL_DCI)
VAR DSTI = LOOKUPVALUE('DataSet'[DataSetTypeIndex], 'DataSet'[CreatedPeriodIndex], BL_CPI, 'DataSet'[DC_Index], BL_DCI)
VAR SublotFilter = SELECTEDVALUE('Sublot'[Sublot])

RETURN
SWITCH(
    TRUE(),
    SELECTEDVALUE('Calendar'[Date]) <= MAXPLAN,
        SUMX(
            FILTER(
                ALL(ProdQuantsPivotPlan),
                ProdQuantsPivotPlan[Resource] = RES &&
                ProdQuantsPivotPlan[Author] = Auth &&
                CONTAINS(YrMth, [Month Year], ProdQuantsPivotPlan[Month Year]) &&
                ProdQuantsPivotPlan[DataSetTypeIndex] = DSTI &&
                (
                    ISBLANK(SublotFilter) || ProdQuantsPivotPlan[Sublot] = SublotFilter  -- Filter on sublot if selected, otherwise include all
                )
            ),
            [Value]
        )
)


Cum_ProdQuantsActual =
VAR MAXACTUAL = CALCULATE(MAX(ProdQuantsPivotActual[Date]))
RETURN
SWITCH(
    TRUE(),
    SELECTEDVALUE('Calendar'[Date]) <= MAXACTUAL,
        CALCULATE(SUM(ProdQuantsPivotActual[Value]),
            FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))
    )
Cum_ProdQuantsForecast =
VAR MAXACTUAL = CALCULATE(MAX(ProdQuantsPivotActual[Date]))
VAR MAXFORECAST = CALCULATE(MAX(ProdQuantsPivotForecast[Date]))
RETURN
SWITCH(
    TRUE(),

    SELECTEDVALUE('Calendar'[Date]) > MAXFORECAST,
    SELECTEDVALUE('Calendar'[Date]) > MAXACTUAL,
        CALCULATE(SUM(ProdQuantsPivotActual[Value]),
    FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))
+
CALCULATE(SUM(ProdQuantsPivotForecast[Value]),
    FILTER(ALLSELECTED('Calendar'), 'Calendar'[Date]<=MAX('Calendar'[Date])))
    )


Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Use a disconnected table for the x axis. Calculate your measure for each row of that table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you lbendlin
    Hi, Sruthychinnan 

     As you can show, your measure does not return data when it is calculated to the corresponding month of 20241, 12, and 2025.
    In this case, you can solve the current problem from the following points:

    If the data for the previous month is correct, and the value month after you calculate correctly, then there is no problem with your measure, and the main reason is that there is no corresponding data for those months.

    If you don't have the corresponding data for these months, you can follow the method below to connect your polylines. I've created a simple example of data:

    To solve this situation of disconnecting, I created an index column that will do a sort of my column column.

    This is a workaround for connecting polylines in cases where you really don't have data for a few months.
    If in your case the metric is calculated to the point where it should have data for those months, then you can check your DAX step by step, such as Super User with a disconnected table.

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.