cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Regular Visitor

## 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?

1 ACCEPTED SOLUTION
Super User

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))``````

Proud to be a Super User!

5 REPLIES 5
Regular Visitor

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

Super User

you are welcome

Proud to be a Super User!

Super User

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)``

Proud to be a Super User!

Regular Visitor

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.

Super User

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))``````

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors