Using "Between" function for date ranges

building reports
1

Hi,

I am just looking for a bit of help please as I am getting errors. I am trying to create a variable that gives me an output for each tax year. So my report is looking at a date range spanning 6 years and based on the transaction dates, I want an output to say which tax year it relates to. To start with I just tried to use the 2020/21 tax year as a starting point but I am running into issues.

The formula I’ve used for this is

=If [Car Allow].[Transaction Date:Payroll] Between (01/04/2020;31/03/2021) Then “Tax Year 2020/2021”

Any ideas?
Thanks

2

Ideally you will add a date time in the database where you would have this as a column and create an object out of that. If that is not possible then next best thing is to add this as a sql case when statement in the universe as an object.

If you want to solve it in the report (and need to recreate this in every report that you are creating), then this could be the approach. To what kind of issue are you facing?

3

I’d push this back to the universe if you can.

If you have a calendar table, add a financial year column to it and persist it through to the universe as a new dimension object in your date class.
If you want a quick and dirty fix, you could simply use month numbers to create a string:
="Tax Year "+If(MonthNumberOfYear([Car Allow].[Transaction Date: Payroll]) >= 4;FormatNumber(Year([Car Allow].[Transaction Date: Payroll]);"0")+"/"+FormatNumber(Year([Car Allow].[Transaction Date: Payroll])+1;"0");FormatNumber(Year([Car Allow].[Transaction Date: Payroll])-1;"0")+"/"+FormatNumber(Year([Car Allow].[Transaction Date: Payroll]))

I think that’s right, but I’ve not got Webi in front of me right now to check.

4

Thanks both for your responses.

Will go down the variable formula route if possible :+1:

5

The responses thus far are good suggestions. If we just stick with what you are trying I think we can get rid of the errors.

I am assuming the data type of your [Car Allow].[Transaction Date:Payroll] object is Date time.

Then you need to convert your tax year range values to dates as well…

=If [Car Allow].[Transaction Date:Payroll] Between (ToDate("01/04/2020"; "dd/MM/yyyy"); ToDate("31/03/2021"; "dd/MM/yyyy")) Then "Tax Year 2020/2021"

But that will just get you one year. So would need to create a nested if statement with all a 6 of your tax year ranges and then change it next year to include the new tax year. Or go with @MarkP’s suggestion to create a dynamic variable.

6

Have you tried using the built in time dimension feature? Use the “fiscal year” start to set the start of your year to April.