cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Filter year in a calculate function

Hi Gurus,

I want to filter Year from a measure using CALCULATE function.

below is the measure I want, it work fine without Year >= "2021" how could I work this one

Measure =

VAR Result = CALCULATE(
SUM('FACT-Data'[NUMARIC]),
'DIM-Matrics'[MATRIX_ID] = 14,
'Date'[Year] >= "2021"
)

RETURN
Result

1 ACCEPTED SOLUTION
Super User

What is the data format of Date[Year]? If you have any Date column with proper format date like YYYY-MM-DD or any other format so try below DAX:

Measure =
VAR Result = CALCULATE(
SUM('Table'[Sales]),
'Table'[ID] = 14,
YEAR('Table'[Date]) >= 2020
)
RETURN
Result

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
3 REPLIES 3
Community Champion

Try the following formula:

``Measure = CALCULATE(SUM('FACT-Data'[NUMARIC]),FILTER('DIM-Matrics','DIM-Matrics'[MATRIX_ID] = 14),FILTER('Date','Date'[Year] >= "2021"))``
Helper V

thanks a lot for the reply. It did not work for me.

my requirement was to return blank when the data for that year is blank. Manged to get it done with a simple IF statment :).

Thanks a lot

Super User

What is the data format of Date[Year]? If you have any Date column with proper format date like YYYY-MM-DD or any other format so try below DAX:

Measure =
VAR Result = CALCULATE(
SUM('Table'[Sales]),
'Table'[ID] = 14,
YEAR('Table'[Date]) >= 2020
)
RETURN
Result

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.