dynamically calculated column

[gringo]

Hello guys,

I'm beginner in DAX and Pbi.

I have a table fact with 2 dates and an amount fileds, like this :

date_insert         date_order         amount
21/12/2017         01/08/2017       5
13/12/2017         01/07/2017       4
01/08/2016         01/08/2015       6
01/08/2013         01/08/2010       2

There is a relation-ship with date dimension on date_insert field. I want to sum all transactions where year date order is the same that max filtered value from date dimension in a mesaure and all transactions where the year date order isn't the same that max filtered value from date dimension in another measure.

For example:

• date dimension is between 01/01/2016 and 31/12/2017.
• The year max value filtered is 2017 The four first line is filtered for my search : The amount is 9 (5+4) for 2017 and 6 for the other year.

What approach can I take?

Thanks for your help

[Greg_Deckler · Accepted solution]

Read this over a couple times, still not certain I know what you want, but probably something like:

Measure = 
VAR __year = MAX('Date'[Year])
RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])=__year),[amount])

Measure 2 =
VAR __year = MAX('Date'[Year])
VAR __maxAllYear = MAXX(ALL('Date'),[Year])
VAR __minAllYear = MINX(ALL('Date'),[Year])
RETURN SUMX(FILTER(ALL('Table'),YEAR([date_order])>=__minAllYear&&YEAR([date_order])<=__maxAllYear&&YEAR([date_order])<>__year),[amount])