Fiscal YTD

[t-atgeor]

Hi,

am trying to create a formula which sums up my costs for a particular fiscal year. Have used the following formula and it works perfectly, nevertheless, once I use a slicer  and choose a specific month, the amount of the field Fiscal YTD changes (it should stay stable).

YTD = TOTALYTD(SUM('Costs'[Costs (USD)]),'Costs '[Fiscal Month], ALL('Costs '),"3/31")

As an example, lets say that my fiscal year finishes on March 31st, since we are now September my YTD will be a sum of all amounts from April 1st to date.  However, If I would like to use a slicer with the field 'Costs' [Fiscal month] and choose January 2016, I would like to see the previous YTD, which should be a total amount from April 1st 2015 to March 31st 2016. How do I have this number fixed, so it shows me the correct sums of YTD? The issue is that if I choose January 2016 it will show me the amount from April 1st 2015 to January 31st 2016, excluding the left 2 months of the fiscal year.

Thank you and Regards,

Atanas

[CahabaData]

here is a para phrase of 1 sentence of your post:

"...would like to ....choose January 2016, .... to see, .... a total amount from April 1st 2015 to March 31st 2016."

So you can't use the embedded YTD feature because it is defined as ....well ...year to date - - and that is not what you seek.

It would appear you seek the prior full year from the end of the quarter of the selected month.  Not sure if your fiscal quarters are the same as calendar quarters.   I am thinking 2 Calculated Columns - first one needs to establish first the end of the quarter for each and then calculate the prior 12 month sum.

[t-atgeor]

Hi @CahabaData,

thanks for responding. Yes, in my case my fiscal year is different from the calendar one, it starts on April 1st and finishes on March 31st. For this reason, I use the formula TotalYTD, and it shows me correctly the amounts, I would like to fix it against any slicers/filters for a specific month, so it read correctly the month and shows me the correct Full fiscal amount (to date).

Thanks ,Atanas

[CheenuSing]

Hi @t-atgeor

1. I am assuming you have a calendar table, if you don't have create one.

2. In the Calendar Table , create columns MonthNumber = MONTH(Calendar[Date])

3. Create column  FiscalYear = If ( [MonthNumber] >= 4, [Year], [Year]-1 ) 

4. Create column FiscalMonth = If ( [MonthNumber] >= 4, [MonthNumber] - 3, [MonthNumber] +9  )

5. Create a measure TotalCost = Sum(Factable[Cost])

6. Create YTDCost = TOTALYTD([TotalCost],'Calendar'[Date])

7. Plot this in a bar graph with FiscalYear as Axis and YTDCost as values.

8. Create a slicer using FiscalYear

9. Create a slicer using FiscalMonth

10. Select Years in FiscalYear and see the graph at step 7.

I think I understood your problem and if this solves your issue please accept it as solution and also give KUDOS.

Cheers 

CheenuSing