Current Fuscal year Sales

[alec1982]

hi guys,

I have a table with

Sales ID     Payment Date     Fiscal year     Sales

knowing that the Fiscal Year  starts on april 1 of each year and ends on march 31 of the next year and we are currently in 2015 FY.

How can I do sum of current FY sales

[Anonymous]

Create a Calendar table that maps calendar date to fiscal date, then use the fiscal date fields in your report.

We do this all the time - more than calendar dates.

[alec1982]

Thank you for your quick answer. I dont have the ability to create fiscal calendar here.

What i really want is a text box with the sum of sales for current fiscal year giving that i have the FY year on my data and the actual payment date and the first and last days in the fy

[Anonymous]

Try putting this in the text box's expression:

=sum({$<[Fiscal Year]={'$(=Max({1}[Fiscal Year]))'}>}Sales)

[cspencer3]

You can also use the InYearToDate() function. Pass it the date you are looking to check if it is in the current YTD, and the  'First month of year' argument (in your case 4 for April). Then just do: SUM(if(InYearToDate(DateField,Today(),0,4),Sales)

InYearToDate (date, basedate , shift [, first_month_of_year = 1])

[alec1982]

Hi,

This doesn't work as there are future sales scheduled for next FY year and using Max will pick up the next year

[alec1982]

Thank you but not sure how can i use this function for to sum Sales for current FY year.. Can you please give me more details or expression that I can use.

[cspencer3]

I used this exact line when trying with some dummy data in a dashboard:

=SUM(if(InYearToDate(PaymentDate,Today(),0,4),Sales) )

Thus, if the PaymentDate is in the YTD (Adjusting 4 months for Fiscal years) then it will be true and return the sales amount. Thus it is then summed up by the SUM function. I tested this to get only PaymentDates that are in the current fiscal year, and not after today(). Therefore it should give me 200 in sales, and thus it did. Does this make sense, and/or applicable in your situation? What does your data look like?

Dummy Data:

    SalesID, PaymentDate, FiscalYear, Sales

    1,           5/1/2014,           2014,           100

    2,           1/1/2014,           2013,           200

    3,           2/1/2014,           2013,           300

    4,           6/5/2014,           2014,           100

    5,           1/1/2013,           2012,           200

    6,           3/2/2015,           2014,           200

[cspencer3]

I also marked down 2014 as the fiscal year, but apparently it is supposed to be 2015. In my line of work the Fiscal year starts 10/1, not 4/1. Just noticed that. But the concept is the same. That will be the Current Year to date, fiscal year calculation.

[alec1982]

Hi Charles,

this doesn't work on my case as I have future dates scheduled for next year on the table and by doing the above all data from the next year will be summed..