financial year

2 Topics

Variable financial year
Hi, My company uses accounting periods that end on the last Friday of the month. This means that the start of the financial year is variable. Eg. This financial year (2022) began on the 26/6/2021 and financial year (2023) will begin on the 25/6/2021. When writing YTD calculations, I have to input a string to define the end of the financial year eg TOTALYTD([Actuals],DimDateandPeriod[Date],ALL(DimDateandPeriod[Date]),"6/30") but this then puts the calcuation off by a few days. I have tried to make a dynamic column to insert instead of "6/30" but the YTD function will not accept it. I have a column in my date table which holds the first day of the financial year and tried doing a calculate between dates of that date and today but that doesn't work either. Does anyone have any other ideas of how to get around this problem? Is there a long hand way of writing YTD where I can have dynamic dates?
Solved
Mo-PHAT
4 years ago Place DAX Commands and Tips
1.9KViews
0likes
2Comments
Achieving financial fiscal year format of YYYY-YY in DAX
Hi Everyone, After much research I cannot seem to find a way to format my date for the UK fiscal year in my custom calendar table i.e YYYY-YY (2021-22). I have code that I use for my calendar table which is below. I have tried editing the code but im a newbie and the format comes out wrong. The best I could get it was YY-YY but this doesnt match my other data. Any one know how I could achieve this? Calendar = --Inputs-- VAR WeekStartsOn = "Mon" VAR FiscalStartMonth = 4 --NOTE: Calendar week starts from Monday --Calculation-- RETURN ADDCOLUMNS ( CALENDARAUTO ( FiscalStartMonth - 1 ), "MIndex", MONTH ( [Date] ), "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), "CalMonth", FORMAT ( [Date], "mmm" ), "CalQtr", "Q" & CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ), "CalYear", YEAR ( [Date] ), "Fiscal Week", VAR FiscalFirstDay = IF ( MONTH ( [Date] ) < FiscalStartMonth, DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ), DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 ) ) VAR FilteredTableCount = COUNTROWS ( FILTER ( SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ), FORMAT ( [Dates], "ddd" ) = WeekStartsOn ) ) VAR WeekNos = IF ( FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn, FilteredTableCount + 1, FilteredTableCount ) RETURN "Week " & WeekNos, "Fiscal Qtr", "Q" & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3, "Fiscal Year", VAR CY = RIGHT ( YEAR ( [Date] ), 2 ) VAR NY = RIGHT ( YEAR ( [Date] ) + 1, 2 ) VAR PY = RIGHT ( YEAR ( [Date] ) - 1, 2 ) VAR FinYear = IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "/" & NY , PY & "/" & CY ) RETURN FinYear, "CalWeekNo", WEEKNUM ( [Date], 2 ), "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ), "Day", FORMAT ( [Date], "ddd" ), "CustomDate", FORMAT ( [Date], "d/mm" ) ) Regards, Matthew
Solved
Matthew77
5 years ago Place DAX Commands and Tips
6KViews
0likes
4Comments