Forum Discussion

Cosik's avatar
Cosik
Helper I
6 years ago
Solved

Calculation with function in filter

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).
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Cosik
      Helper I
      6 years ago

      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.