Forum Discussion

t-atgeor's avatar
t-atgeor
Icon for Microsoft Employee rankMicrosoft Employee
10 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
t-atgeor's avatar
t-atgeor
Icon for Microsoft Employee rankMicrosoft Employee
10 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
10 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