Forum Discussion
Solved
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(MONT...
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))
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))
