Forum Discussion

t-atgeor's avatar
t-atgeor
Microsoft Employee
9 years ago

Fiscal YTD

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 spec...
Need Help
CahabaData's avatar
CahabaData
Memorable Member
9 years ago

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's avatar
t-atgeor
Microsoft Employee
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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