Forum Discussion

IlyaF's avatar
IlyaF
Regular Visitor
4 years ago
Solved

Measure calculate function with few filters

Hi Community,

 

I have the following measure:

 

Abandoned = calculate(
count(
'Apps & Grants'[Grant ID]),
'Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"

)​

 

Now I would like to add another filter with a date, to calculate the same measure only for the current year, based on the 'TimeStamp' column that I have in the table. So I tried the following:

 

Abandoned = calculate(
count(
'Apps & Grants'[Grant ID]),
('Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"),

'Apps & Grants'[Timestamp Entered State]=YEAR(TODAY())

)

 

But it seems to be wrong as I am getting the same result 😞
What am I doing wrong? 

  • AnthonyJoseph's avatar
    AnthonyJoseph
    4 years ago

    Hi IlyaF ,

     

    If you want to filter by the current year then you will have to surround the column (Timestamp Entered State) by year function so the updated measure will be like:

    Abandoned_new = calculate(
count(
'Apps & Grants'[Grant ID]),
('Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"),

Year('Apps & Grants'[Timestamp Entered State])= YEAR(TODAY())

)

     Hope this helps 

     

    Thanks,

    AnthonyJoseph

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    No, that looks syntactically correct.  Check your data - maybe your timestamp is not a numeric year?

     

    By the way you can slightly simplify your filter

     

     

     

    Abandoned = calculate(
count('Apps & Grants'[Grant ID]),
'Apps & Grants'[Current State] IN {"Abandoned","Withdrawn",Declined","To Delete"},
'Apps & Grants'[Timestamp Entered State]=YEAR(TODAY())
)

     

     

    • IlyaF's avatar
      IlyaF
      Regular Visitor
      4 years ago

      Hi lbendlin, thanks for your reply.

      My timestamp field is in a Short Date format:

      And still, when filtering only the 'Current State' field, the result is fine (79).
      But when adding the 'Timestamp' field filter, I get a Blank result while when filtering the data itself manually, the result should be 10.

       

       

  • AnthonyJoseph's avatar
    AnthonyJoseph
    Icon for Resolver III rankResolver III
    4 years ago

    Hi IlyaF ,

     

    If you want to filter by the current year then you will have to surround the column (Timestamp Entered State) by year function so the updated measure will be like:

    Abandoned_new = calculate(
count(
'Apps & Grants'[Grant ID]),
('Apps & Grants'[Current State]="Abandoned"||
'Apps & Grants'[Current State]="Withdrawn" ||
'Apps & Grants'[Current State]="Declined" ||
'Apps & Grants'[Current State]="To Delete"),

Year('Apps & Grants'[Timestamp Entered State])= YEAR(TODAY())

)

     Hope this helps 

     

    Thanks,

    AnthonyJoseph

    • IlyaF's avatar
      IlyaF
      Regular Visitor
      4 years ago

      Hi AnthonyJoseph,

       

      Thanks! This helped 🙂