Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want my bar chart to show ALL years that show YTD counts. Currently using an if statement that works but not as needed. Example: the bar chart shows FY's 2020,2019,2018,2017
Problem:
As soon as the user picks a [Fiscal Year] filter, the remaining years are no shown
The following is the current if statement:
if([Fiscal Year] = $(vFiscalYear),
(Count({$< [Fiscal Year]=, InCurrStartDateYTD= {'1'} >} [SalesContacts])),
if([Fiscal Year] = $(vFiscalYear)-1,
(Count({$< [Fiscal Year]=, In1stPriorStartDateYTD= {'1'} >} [SalesContacts])),
if([Fiscal Year] = $(vFiscalYear)-2,
(Count({$< [Fiscal Year]=, In2ndPriorStartDateYTD= {'1'} >} [SalesContacts])),
if([Fiscal Year] = $(vFiscalYear)-3,
(Count({$< [Fiscal Year]=, In3rdPriorStartDateYTD= {'1'} >} [SalesContacts])),
if([Fiscal Year] = $(vFiscalYear)-4,
(Count({$< [Fiscal Year]=, In4thPriorStartDateYTD= {'1'} >} [SalesContacts]))
)))))
Supporting facts:
vFiscalYear is a variable set for the current FY
The [Fiscal Year] field is from the master calendar
The YTD flags are from the master calendar
It seems this needs to be inside the set analysis only i can't get it to work. Any help would be appreciated.
Thanks team
I would create a dimension during load that flags every date as being YTD regardless of year. Something like
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD]
where $1 is your date field.
Then your chart, with Fiscal Year as the dimension, can use this measure:
Count({$< [Fiscal Year]=, InYTD= {1} >} [SalesContacts])
I would create a dimension during load that flags every date as being YTD regardless of year. Something like
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD]
where $1 is your date field.
Then your chart, with Fiscal Year as the dimension, can use this measure:
Count({$< [Fiscal Year]=, InYTD= {1} >} [SalesContacts])