YTD and Previous YTD based on fiscal year

[abc_18]

Hi,

My fiscal year starts from April.

I'm trying to figure out a simple YTD and previous YTD expression:

The year follows the Fiscal year, so I'm looking for FY 2020 (19/20) 2019-04-01 through 2020-01-27(till yesterday) in comparison to FY 2019 (18/19) 2018-04-01 through 2019-01-28.

Regards

[JustinDallas]

What are you trying to compare? A bar chart, a line chart and so on.  Generally, you have a Master Calendar or a  flag that shows whether or not the date concerned is in the current fiscal year or the previous year.

LET vToday = Daystart(Timestamp(Today()))
;
MasterCal:
...
 If( InYear  (TempDate, '$(vToday)',0),'Y','N') as CY,
 If( InYear  (TempDate, '$(vToday)',-1),'Y','N') as LY,
...

Then, you would use Set Analysis to figure compare counts between the current year and the previous year.

[abc_18]

Hi Justin,

I have to show current YTD and previous YTD in  KPI.

So first KPI should show current YTD and another one previous YTD, and that includes  fmonth, fyear and date selection as well.

I have a calendar script already which includes fiscal year description.

I am trying to achieve these two KPI's using set analysis.

Thanks

[abc_18]

I am using below expression to achieve this:-

sum({<invoice_date=,fYear=,FMonth=, invoice_date={">=$(=YearStart(Max(invoice_date)))<=$(=Max(invoice_date))"}>}qty)

This gives me 2020-01-01 till 2020-01-28, but the output should be 2019-04-01 till 2020-01-28, and for previous 2018-04-01 till 2019-01-28, based on fiscal year.

below is the calendar script which I am using:-

Set vFM = 4 ; // First month of fiscal year
Calendar:
Load Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
*;
Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year
Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month
*;
Load Year(invoice_date) as Year, // Your standard master calendar
Month(invoice_date) as Month,
num(Month(invoice_date)) as monthno,
Date(Monthstart(invoice_date), 'MMM-YYYY') as MonthYear,
invoice_date
Resident XYZ;

This invoice_date field is linked to data model .

Regards

[Vegar]

You could try something like this. Assumming you want to calculate fiscal year based on the in max(invoice_date) in your selection.

Last f year

=Sum({<fYear,FMonth,Year,Month,invoice_date={">=$(=YearStart(max(invoice_date),-1,4))<$(=YearEnd(max(Date),-1,4))"}
>}Amount)

This f year

=Sum({<fYear,FMonth,Year,Month,invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(=YearEnd(max(Date),0,4))"}
>}Amount)

[abc_18]

Hi Vegar,

Thanku very much for quick response.

I tried the expression suggested by you:-

=Sum({<fYear,FMonth,Month,invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(=YearEnd(max(invoice_date),0,4))"}
>}qty)

but this gives me date range  2019-04-01 to 2020-03-31, the till data should be till yesterday that is 2020-01-28

similarly for previous year till date should be 2019-01-28, currently it's giving 2019-03-31.

Please suggest

[Vegar · Accepted solution]

You are absolutely correct. I'm sorry but I must have missread your question.

Try these expressions instead.

YTD:
=Sum({<
fYear,FMonth,Month, Year, invoice_date={">=$(=YearStart(max(invoice_date),0,4))<$(= addyears(max(invoice_date), 0))"}
>}Sales)

Previous YTD:
=Sum({<
fYear,FMonth,Month,Year,invoice_date={">=$(=YearStart(max(invoice_date),-1,4))<$(= addyears(max(invoice_date), -1))"}
>}Sales)

[abc_18]

Hi Vegar,

Thank you very much for help 🙂  , working as expected.

Regards