Dynamic Fiscal Year

[Unknowncharact0]

Hello,

I am trying to create a dynamic count # of cases closed in a fiscal year (October to September)

Current Fiscal Year Count =
VAR CurrentDate = TODAY()
VAR CurrentFiscalYear =
IF(MONTH(CurrentDate) >= 10, YEAR(CurrentDate), YEAR(CurrentDate) - 1)
RETURN
CALCULATE(
COUNT('Fact Table'[Case #]),
FILTER('Fact Table', YEAR('Fact Table'[Date Case Closed]) = CurrentFiscalYear))

This DAX does not seem to work correctly, can anyone help?

[Unknowncharact0]

Thank you so much, I appologize in my response delay!

[ryan_mayu]

you are welcome

[ryan_mayu]

@Unknowncharact0 

could you pls proivde some sample data and expected output?

maybe you can create a date table and create a column

Column = if(month('Table'[Date]) in {10,11,12},YEAR('Table'[Date]),year('Table'[Date])-1)

[Unknowncharact0]

https://1drv.ms/u/s!Anb0OMw34A1igQDHuXJI-0W4wixD?e=R5R5Bx

Thank you so much for your reply! See if this works for PBI sample, the idea is that I want to have a KPI card that will compare current year to previous year count and it rolls over automatically at the beginning of new year instead of me adjusting the measure.

[ryan_mayu · Accepted solution]

is this what you want?

Count of Case for 2023 = 
VAR _year=CALCULATE(max('DIM Calendar'[Fiscal Year]),ALL('DIM Calendar'))-1
return 

	CALCULATE(COUNT('Fact Table'[Case]),
	FILTER(all('DIM Calendar'),'DIM Calendar'[Fiscal Year]= _year))

Count of Case for 2024 = 
VAR _year=CALCULATE(max('DIM Calendar'[Fiscal Year]),ALL('DIM Calendar'))
return 

	CALCULATE(COUNT('Fact Table'[Case]),
	FILTER(all('DIM Calendar'),'DIM Calendar'[Fiscal Year]= _year))