Forum Discussion

vijay273162's avatar
vijay273162
Icon for Helper III rankHelper III
4 years ago
Solved

Cumulative DAX affecting filter context used in Custom Measure

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 ?

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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