Forum Discussion
Helper INumbering 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
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 ) + 1Results
Hope this helps! 🙂
1 Reply
- littlemojopuppy
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 ) + 1Results
Hope this helps! 🙂