CALCULATE with Measure as Filter (better workaround needed)

[karo]

Hi,

I wonder if anyone can help me in this case.  I am struggling with calculations to create chart for Full Year Estimation, where for past months I would like to show Actuals values and for the rest months Forecast and compare both data against Budget  at the same chart.  The problem is that my current measure (please see an example below) needs to be updated manually every month (bold part).

Forecast  = CALCULATE(CALCULATE([Total];Table_1[Financial element]="Forecast");Table_1[End of Month]>(EOMONTH(DATE(2018;08;10);-1)))

When I try to replace this part with Measure I get an error(A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.). I have several measures with this trouble ending to show different parts and types of forecast at the same chart. The satisfactory solution will be if I need change this only in one place, not in every relevance calculation. The date unfortunately is not stable over a year and more over not in line with standard calendar.

Any hints?

Thank you in advance for your help,

Karo

[karo]

Hi,

I wonder if anyone can help me in this case.  I am struggling with calculations for Full Year Estimation, where for past months I would like to show Actuals values and for the rest months Forecast and compare both data against Budget  values at the same chart.  The problem is that my current measure (please see an example below) needs to be updated manually every month (bold part).

Forecast  = CALCULATE(CALCULATE([Total];Table_1[Financial element]="Forecast");Table_1[End of Month]>(EOMONTH(DATE(2018;08;10);-1)))

When I try to replace this part with Measure I get an error (A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.). I have several measures with this trouble ending to show different parts and types of forecast at the same chart. The satisfactory solution will be if I need change this only in one place, not in every relevance calculation. The date unfortunately is not stable over a year and more over not in line with standard calendar.

Any hints?

Thank you in advance for your help,

Karo

[Zubair_Muhammad · Accepted solution]

@karo

Try storing the MEASURE value in a variable first them use that variable in the CALCULATION

Fro example

Forecast =
VAR mymeasure = [Measure]
RETURN
    CALCULATE (
        CALCULATE (
            [Total],
            Table_1[Financial element] = "Forecast",
            Table_1[End of Month] > mymeasure
        )
    )

[karo]

Thank you a lot @Zubair_Muhammad ! It is working perfectly