cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Getting ERROR A function 'CALCULATE' has been used in a True/False expression that is used...

Hello All,

 

I would like to show the card values as 

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

like

9.PNG

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 onemore thing is I dont want to use the slicer anymore. Actually I am not going to use the slicer.

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

 

i would like to give info of what kind of data that i am having.

I am having table's plan rev, actual rev, and MonthYear

I have creatd relashionship using Date column from MonthYear table to plan rev and actual rev Date coloumns.

MonthYear table

1.PNG

 

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

Then written measures for

 

 

PlanSum = SUM('Revenue Report'[Planned Rev])
PlanPrevMon = CALCULATE([PlanSum],PREVIOUSMONTH('Month Year'[Date]))

 

 

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. Getting the error as

 

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

Mohan V

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Could you try the formula below to see if it works? Smiley Happy

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
    )

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Could you try the formula below to see if it works? Smiley Happy

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
    )

 

Regards

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors