Default to current year but enable user to select from year dropdown list as well

[rchiew604]

Have a gauge chart on my dashboard that does the following calculation:

 sum({<[Ledger Type]={'BA'}   
  ,Source={'F0902'}
        ,[JDE.Year] = {$(=$(vV_CurrentYear))}
        >}Amount/100)

vV_CurrentYear = Year(Today()) so the chart is currently defaults to the current year.

However, we'd also like to enable the users to select other year from the 'Year' filter pane. Please note, they can only select one single year from the filter but not multiple years.

Please let me know how to achieve this via set analysis.

Thanks in Advance.

[rchiew604]

Upon reviewing list of years in JDE.Year, found out the most recent year is actually 2020, not 2019. Believe some of the transactions were future dated hence the future year. So the max function is looking at 2020 which shows as zero amount instead of 2019.

[rwunderlich]

so perhaps:

JDE.Year = $(=RangeMax(max(JDE.Year, Year(today(2))))

or

JDE.Year = $(=Alt(only(JDE.Year), $(vCurrentYear))

[rchiew604]

So [JDE.Year] = {$(=max([JDE.Year]))} works but it is looking at max year in our list which is 2020. Hence it is showing zero.

Modified the expression to the following as you suggested but now it yields no data at all:

[JDE.Year] = {$(=RangeMax(max([JDE.Year],Year(today(2)))))}

Is my syntax incorrect?

[rwunderlich]

My mistake. I meant RangeMin(). 

-Rob

[rchiew604]

Is it like this?

[JDE.Year] = {$(=RangeMin(min([JDE.Year],Year(today(2)))))}

Still yield no results....

[rwunderlich]

Please post the entire expression.

-Rob

[rchiew604]

Rob,

See below:

num(sum({<[Ledger Type]={'AA'}
  ,Source={'F0911'}
   ,[JDE.Year] = {$(=RangeMin(min([JDE.Year],Year(today()))))}
      // ,[JDE.Year] = {$(=max([JDE.Year]))}
        >}Amount/100),'#,##0')

[rwunderlich · Accepted solution]

Get rid of the min().

num(sum({<[Ledger Type]={'AA'}
  ,Source={'F0911'}
   ,[JDE.Year] = {$(=RangeMin([JDE.Year],Year(today())))}
      // ,[JDE.Year] = {$(=max([JDE.Year]))}
        >}Amount/100),'#,##0')

[rchiew604]

That worked!

Thank you Rob!

[rchiew604]

Rob

RangeMin expression works in my case.

However, just curios abut the logic behind it. Isn't it supposed to be RangeMax that returns the highest number in my JDE.Year list which is 2019?