Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
Hi AnthonyJoseph,
Thanks! This helped 🙂
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())
)
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |