Calculation with function in filter

[Cosik]

Hi,

I try to add dynamic filter to the calculation but i receiving error.

This is the function:

DPA = CALCULATE

(COUNTROWS(VALUES('MDS current'[WO #])),

'MDS current'[WO Status]="COMPLETED",

'MDS current'[RFS Quater]=ROUNDUP(MONTH(TODAY())/3, 0),

'MDS current'[RFS Year]=YEAR(TODAY()))

When i enter static filter value in:

'MDS current'[RFS Quater]=ROUNDUP(MONTH(TODAY())/3, 0)   --- 'MDS current'[RFS Quater]="2"

'MDS current'[RFS Year]=YEAR(TODAY())  ---- 'MDS current'[RFS Year]= "2020"

 the function working fine.

I dont know why i receiving error.

Both function ROUNDUP(MONTH(TODAY())/3, 0) and YEAR(TODAY()) giving me the same value as above.

I try also add those function in VALUE function but the result was the same (error).

[mahoneypat · Accepted solution]

Your static example has the quarter as a text value ("2"). Your roundup function is returning an integeter.  You'll need to convert it to a text value with FORMAT() (or change your quarter value to whole number).  Also, you can pre-calculate your comparison values as variables first, as follows.  This sometimes solves some errors inside calculate.

DPA =
VAR quartervalue =
FORMAT(ROUNDUP ( MONTH ( TODAY () ) / 3, 0 ), "General Number")
VAR thisyear =
YEAR ( TODAY () )
RETURN
CALCULATE (
COUNTROWS ( VALUES ( 'MDS current'[WO #] ) ),
'MDS current'[WO Status] = "COMPLETED",
'MDS current'[RFS Quater] = quartervalue,
'MDS current'[RFS Year] = thisyear
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Cosik]

Thx for help, probably working.

I find the problem, my column were not converted to any specyfic type. I change to number and the formula working.

It is quite a strange that i need to do this while there are only numbers.