cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matthew77
Regular 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 Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors