Forum Discussion
Dynamic Fiscal Year
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?
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))
5 Replies
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)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.
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))
Thank you so much, I appologize in my response delay!
you are welcome
