Converting Date and Time to Financial Year and Quarter/Month (Australian Date)
Hi All, I currently have a column (StartDate) that has values in the following format: DD/MM/YYYY HH:MM:SS AM/PM E.g. 14/08/2022 2:00:00 PM I was hoping to accomplish conversions through DAX commands but wasn't really sure how to approach it, New Column 1 (Month & Financial Year) 14/08/2022 2:00:00 PM --> Aug FY22 New Column 2 (Quarter and Financial Year) 14/08/2022 2:00:00 PM --> Q1 FY22 Any help or guidance would be appreciated!
Fiscal week not on the first day of month
Hi all, I'm trying to add Fiscal Week and Month to my date table. It starts on October. I used blog post by ChandeepChhabra which I found on a solved question on this website, to generate the Fiscal Week column and it worked but not my desired result. Question Link - https://community.powerbi.com/t5/Desktop/Creating-a-fiscal-week-column/m-p/556549 Blog Post Link - https://www.goodly.co.in/calculate-fiscal-week-in-power-bi/ This was the result I get when I used it. My desired fiscal week is a little different, it is not on the first day of the month. Here's an example: As you can see, the first week starts on the 3rd of Oct where as the result I got from refering to the post starts on 1st of Oct. If the first 2 days of the month is at the end of the week then it is considered to be the week of previous month. I would also like to add another column for the Month based on the fiscal week. Below is my desired Dim Date table. Date Work Week Month (WW) 10/1/2021 12:00:00 AM WW 52 September 10/2/2021 12:00:00 AM WW 52 September 10/3/2021 12:00:00 AM WW 1 October 10/4/2021 12:00:00 AM WW 1 October
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, MatthewUK Fiscal Year Column
Hi, I am trying to create a Date format Table which has a column for UK fiscal year based on another column date. I have managed to do from 01 April - 31 March using month calcs but need the fiscal year dates to be 06 Apr - 05 Apr in acordance with UK fiscal year. The formula i have at the moment is as follows: Fiscal Year = IF(MONTH(Date_Reference_Table[Date])>3,YEAR(Date_Reference_Table[Date])&"-"&YEAR(Date_Reference_Table[Date])+1,YEAR(Date_Reference_Table[Date])-1&"-"&YEAR(Date_Reference_Table[Date]))
