Need to filter data based upon Condition

[Dhanashree]

Hi All,

I have one column which contains date in 'DD/MM/YYYY' format. That column data type is 'String'.

I want to take count of records where year of date >= 2017.

How to write expression in measure?

Can someone please help to resolve it

Thanks,

Dhanashree

[jonathandienst]

That's tricky with string dates. If your data set is small, you can use Sum(If()):

Count(Distinct If(Right(DateS, 4) >= 2017, IDS))

But this may perform poorly in a large data model. Replace DateS and IDS with valid fieldnames.

The better approach is to convert the date string field to a proper date type (which is a numeric value formatted as a date) during load:

LOAD ...
Date(Date#(DateS, 'DD/MM/YYYY')) as Date,
Year(Date#(DateS, 'DD/MM/YYYY')) as Year,
...

Then your expression would be:
Count({<Year = {">=2017"}>} Distinct IDS)

[Dhanashree]

Thanks Jonty 🙂 It's working