PowerBI Getting ERROR A function 'CALCULATE' has been used

Question

I would like to show the card values:

if current month value is not available then it should compare with the previous month value automatically.

Like

In above image Apr-2017 value is not available then it should compare with the Mar-2017 value.

If Mar-2017 value also not available then the previous month value.Keep on goes. And one more thing is I don't want to use the slicer anymore. Actually I am not going to use the slicer.

I just want to show the month comparison value in a card visuals.

I would like to give info of what kind of data that I have. I have table's plan rev, actual rev, and MonthYear

I have created a relationship using Date column from MonthYear table to plan rev and actual rev Date columns.

MonthYear table

And in plan rev, month year, plan rev, date, and in actual rev actual rev, month year, and date columns.

Then written measures for

 For Sumof Plan rev-->
 PlanSum = SUM('Revenue Report'[Planned Rev])
   For Prev month value-->
 PlanPrevMon = CALCULATE([PlanSum],PREVIOUSMONTH('Month Year'[Date]))

For Start and End Date of the months

FILTERDATESTART= FIRSTDATE('MonthYear'[Date])
FILTERDATEEND= LASTDATE('MonthYear'[Date])

Then for current month PlanArrows measure is.

PlanArrows = CALCULATE(
    IF(
        ISBLANK([PlanSum]),"No data Available",[PlanSum]
    )& " "& IF(
        [PlanSum]=[PlanPrevMon],
        "",
        IF([PlanSum]>[PlanPrevMon],
            UNICHAR(8679),
            UNICHAR(8681)
        ) & IF([PlanSum]<=0,
        "",""
        )
    ),
    'Month Year'[Date]>=FILTERDATESTART, 
        'Month Year'[Date]<=FILTERDATEEND
)

But it is not working. I'm getting 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.

Any Suggestions please,

Thanks

Mohan V

Bunny Bunny · Accepted Answer · 2017-05-18T11:21:32

I think i got the solution on my own. I have written measure which solved this issue.

PlanArrows =
VAR s = [FILTERDATESTART]
VAR e = [FILTERDATEEND]
RETURN
    CALCULATE (
        IF ( ISBLANK ( [PlanSum] ), "No data Available", [PlanSum] ) & " "
            & IF (
                [PlanSum] = [PlanPrevMon],
                "",
                IF ( [PlanSum] > [PlanPrevMon], UNICHAR(8679), UNICHAR(8681) )
                    & IF ( [PlanSum] <= 0, "", "" )
            ),
        'Month Year'[Date] >= s
            && 'Month Year'[Date] <= e
    )

PowerBI Getting ERROR A function 'CALCULATE' has been used

1 Answers