cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Numbering Days in FY

I have a feeling there is a simple solution to this yet for some reason I can’t seem to find it. There’s plenty of info on counting month and weeks within a year. I want to number the days in a FY, where my FY starts on 7/1. I did this for CY I just can’t get it for FY.

I have created a calendar table with DAX. I am trying to number the days in a fiscal year. I was able to do it in a calendar year. So it will number 1/1 of each year as 1 etc. When I try it for fiscal year it works until it gets to 12/31. Then it labels 1/1 as -180 and starts counting backwards it reaches 6/30, which it labels as 0.
Any ideas on how to fix this?
Thx

1 ACCEPTED SOLUTION
Community Champion

Hi @Nick555

Add two calculated columns to your date table.  The first being for a given date fiscal year:

``````Fiscal Year =
IF(
MONTH('Date Table'[Date]) < 7,
YEAR('Date Table'[Date]),
YEAR('Date Table'[Date]) + 1
)``````

The second calculates the number of days since the earliest date with that fiscal year

``````Days Since Start of Fiscal Year =
VAR CurrentFiscalYear = 'Date Table'[Fiscal Year]
VAR StartOfFiscalYear =
CALCULATE(
MIN('Date Table'[Date]),
FILTER(
ALL('Date Table'),
'Date Table'[Fiscal Year] = CurrentFiscalYear
)
)
RETURN

DATEDIFF(
StartOfFiscalYear,
'Date Table'[Date],
DAY
) + 1``````

Results

Hope this helps!  🙂

Community Champion

Hi @Nick555

Add two calculated columns to your date table.  The first being for a given date fiscal year:

``````Fiscal Year =
IF(
MONTH('Date Table'[Date]) < 7,
YEAR('Date Table'[Date]),
YEAR('Date Table'[Date]) + 1
)``````

The second calculates the number of days since the earliest date with that fiscal year

``````Days Since Start of Fiscal Year =
VAR CurrentFiscalYear = 'Date Table'[Fiscal Year]
VAR StartOfFiscalYear =
CALCULATE(
MIN('Date Table'[Date]),
FILTER(
ALL('Date Table'),
'Date Table'[Fiscal Year] = CurrentFiscalYear
)
)
RETURN

DATEDIFF(
StartOfFiscalYear,
'Date Table'[Date],
DAY
) + 1``````

Results

Hope this helps!  🙂

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors