YTD Select Based On the Month Selection

[sat_tok52]

Hi All,

As per my requirement YTD select based on the month selection i.e , suppose  if the user select March month the YTD data should show data from jan-01-2019 to Mar-31-2019 and if user has selected Jan Month then data should show Jan-01-2019 to Jan-31-2019 and No Selection,then data should show from Jan-01-2019 to till date.

Any Help.

Thanks.

[colinh]

The most flexible way of doing this is to create a numeric PeriodNo for comparisons.  In your load script, create a field something like:

autonumber(monthname(Date)) as PeriodNo,

and make sure to order by Date.

Then in your expression you can get YTD by doing:

=sum({$<[Year]={$(=max([Year]))}, Month=, PeriodNo={"<=$(=(max(PeriodNo)))"} >} [#Sales])

Having PeriodNo means you can easily do YTD, compare one month to the previous month, compare 12 months to the previous 12 months, etc.

[Vegar]

You don't need PeriodNo to achieve this, you can use Month if you defined it by the Month() function. If so it is an dual containing both a numeric and a string value.

Try: Sum({$<[Year]={$(=max([Year]))}, Month={"<=$(=(max(Month)))"} >}ValueField)