Filter Non-Measure Column

Question

Greetings, so my Data looks somewhat like this:

with 1 FAW_DATE for 1 YEAR,

Now I'm trying to put FAW_DATE into workbook via simple calculation, it's throwing this error:

I understand the logic behind the same, but what I expect is to show "Month" out of FAW_DATE where Year = '2024'.

Your insights will be highly appreciated 😊

Gianni Ceresa · Answer

Do not mix the 2 key concepts: a calculation and a filter.

If you want to filter YEAR = YEAR( your parameter ), then do this as a filter: it can be a filter in the workbook, it can be a filter in the visualization where you need it.

Then if you need to extract the month from a date, you can do that with a calculation.

But you can't really "filter" the dataset exposed to a workbook via formulas based on a parameter: it would return you values when matching your filter, NULLs when not matching the filter (and NULL is still a value ! ).

Vismay Barot · Answer

You're right @SteveF-Oracle , as I said I understand how the formula works, and "FAW_DATE" isn't a Measure, basically I'll use Month(FAW_DATE) in a Formula.

@Gianni Ceresa , this is the Final Formula:

FILTER(MONTH(FAW_DATE) using YEAR = YEAR(@parameter("To Date")(Date '2024-01-01')))

Just to decipher it to you, End User will select a "To Date" from Filter, then Year has to extracted from that date. The extracted year needs to be compares to Year Column in 1st Snapshot to reach to Date that will output Month.

In simple SQL Language, I would like to:

select Month(FAW_DATE) where Year = Year (Selected-Date) from Quota

I hope this make sense to you.

Also, I tried using FILTER(MAX(MONTH(FAW_DATE)) using YEAR = YEAR(@parameter("To Date")(Date '2024-01-01')))

But what it basically does is take Maximum of FAW_DATE without filtering Year='2024'. For example, it return 10 in 1st snapshot scenario, rather than 3.

Gianni Ceresa · Answer

Hi,

What should that calculation return for records with a date in 2023?

You could use CASE WHEN "Year" = '2024' THEN MONTH("FAW_DATE") END

But that calculation will return NULL for any other date not matching the year = 2024.