Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nick555
Helper I
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

Nick555_0-1659561240686.png

 

1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
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

littlemojopuppy_0-1659571640420.png  

littlemojopuppy_1-1659571676309.png

 

Hope this helps!  🙂

 

View solution in original post

1 REPLY 1
littlemojopuppy
Community Champion
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

littlemojopuppy_0-1659571640420.png  

littlemojopuppy_1-1659571676309.png

 

Hope this helps!  🙂

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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