Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Unknowncharact0
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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Unknowncharact0
Regular Visitor

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

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

11.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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