Forum Discussion

Nick555's avatar
Nick555
Icon for Helper I rankHelper I
4 years ago
Solved

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, w...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    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!  🙂

     

littlemojopuppy's avatar
littlemojopuppy
Icon for Community Champion rankCommunity Champion
4 years ago

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!  🙂