Cumulative DAX affecting filter context used in Custom Measure

[vijay273162]

Hi All,

Below is my Code for Cumulative Sales, used on top of Custom_Sales variable

VAR Forecast_CY-Q =
CALCULATE ( [Forecast Value],
           REMOVEFILTERS (Calendar [Running Days]) ---- I need to use removefilter.
)

VAR Custom_Sales =
IF ( Calendar [ Date ] < today(),
        [Sales_PY-Q] + Forecast_CY-Q ,
        [Sales_CY-Q]
)

VAR QTD_CY =
CALCULATE (
     SUMX (
                 SUMMARIZE ( Calendar, Calendar[Running Days], “@Custom Sales”, Custom_Sales
                ), [@Custom Sales],
      FILTER ( ALLSELECTED ( Calendar),
                  Calendar [Running Days] <= MAX ( Calendar [Running Days]
      )

Return
QTD_CY

------ This QTD_CY is not returning expected result, after today (values are higher)
------ It Seems using variable - Forecast_CY-Q filter context fails when used in cumulative calculation.

How to write cumulative calculation, without affecting the filter context used in custom Measures ?

[vijay273162 · Accepted solution]

Thanks for your explanation about variable.

I have fixed the issue now, after some research. 

can you please look at this :........ link : 

Solved: Re: Calendar Table help - Microsoft Power BI Community 

it is open, since my solution is not best.

[v-jingzhang]

Hi @vijay273162 

Variables are only evaluated once. After it is evaluated in its definition code, it behaves like a static value in other DAX expression. In your measure, Custom_Sales has calculated a value. When it's used in the cumulative expression of QTD_CY, the static value is summarized for every running day. It means it returns the static value for every running day in the table returned by SUMMARIZE function. 

Currently the expected output is not clear. Can you please provide some mock data to show the table you have and what expected result should be like? We need to modify your measure or create a new measure based on the mock data and expected output. 

Best Regards,
Community Support Team _ Jing