cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matthew77
Frequent Visitor

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" )
    )

 

Capture.PNG

 

 

 Regards,

Matthew

1 ACCEPTED SOLUTION

@Matthew77 , sorry one small change

 

Year = year(startofyear([date], "6/30")) & "-" & format(endofyear([date], "6/30"),"YY")

 

or

 

Year = format(startofyear([date], "6/30"),"YYYY") & "-" & format(endofyear([date], "6/30"),"YY")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@Matthew77 , Based on the end date of year

Year = year(startofyear([date], "6/30")) & "-" & year(endofyear([date], "6/30")) 

 

Change year-end 6/30 to your year-end date 

 

this will not work correct if you calendar do not have start and end date.

 

I think I discussed that in my video

https://www.youtube.com/watch?v=vlm5_MN6M4Y

https://www.youtube.com/watch?v=LbstAOFD2Vo



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for your response Amitchanda, i tried that and also watched your videos. Unfortunately this gives the year in the format of yyyy-yyyy (2021-2022) but I need it to be in the format of yyyy-yy (2021-22). 

@Matthew77 , sorry one small change

 

Year = year(startofyear([date], "6/30")) & "-" & format(endofyear([date], "6/30"),"YY")

 

or

 

Year = format(startofyear([date], "6/30"),"YYYY") & "-" & format(endofyear([date], "6/30"),"YY")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

This is perfect. Thank you so much 😁

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors