Multiple Financial Years in a Calendar Table in format YYYY/YY

[BINewbie1]

Hi there,

I'm building a Calendar table in DAX for the first time. It needs to have columms for two different Financial Years :

UK - 1 April to 31 March

My Organisational FY 1 July - 31 June

Could someone help me with the DAX code for these two new colummns? Then I can add the columns for quarters. The Date field is just called Date. So I currently have, following the add column fuction, in a list, of other new columns:

"UK Financial Year", YEAR(EDATE([Date], -3))

This gets the end year change over right, but just displays as a single year - so 31st March 2022, shows just as 2021. I want it to output 2021/22.

Then I'll want to do similar to display the two different sets of Quarters in YYYY/YY QX format. 

Any thoughts?

Many thanks,

Adam

[BINewbie1]

Something like this is what I'm thinking:

"UK Financial Year", IF(MONTH([Date]<=3), YEAR([Date]-1) & "/" & YEAR([Date]), YEAR([Date]) & "/" & YEAR([Date]+1) )

There seems to be an error, the -1 and +1 for the year either side of the / don't seem to be doing anything. Do need to use an ADD and SUBSTRACT type Function or something?

Many thanks,

Adam

[OwenAuger · Accepted solution]

Thanks for posting your existing code!

I have edited the code below, without wanting to change your code too much 🙂

The main points are:

• For the Financial Year, rather than testing whether the month is <= 3, I suggest shifting the date by +9 (or -3) months and taking the year of the resulting date.
• In the code below, I stored this date in a variable RefDate, and the financial year in a variable FY.
• To get the final text value for the financial year, subtract 1 from FY and append "/" and the last two digits of FY (using MOD).
• You can add quarters in a similar way.
• Also, I made a tweak to "Year Month Sort" so that the month always has two digits, otherwise it will not sort correctly, e.g. 201901 ... 201912

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2049, 12, 31 ) ),
   
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month Num", MONTH ( [Date] ),
    "Week Num", WEEKNUM ( [Date] ),
    "Week Day", WEEKDAY ( [Date] ),
    "Day", DAY ( [DATE] ),
    "Month", FORMAT ( [Date], "Mmmm" ),
    "Day Name", FORMAT ( [Date], "dddd" ),
    "Quater Number", "Q" & QUARTER ( [Date] ),
    "Year Month",
        YEAR ( [Date] ) & " "
            & FORMAT ( [Date], "Mmmm" ),
    "Year Month Sort", YEAR ( [Date] ) & FORMAT ( MONTH ( [Date] ), "00" ),
    "Year Quarter",
        YEAR ( [Date] ) & " " & "Q"
            & QUARTER ( [Date] ),
    "Year Quarter Sort", YEAR ( [Date] ) & QUARTER ( [Date] ),
    "1 Month to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -1 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "3 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -3 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "6 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -6 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "12 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -12 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "UK Financial Year",
        VAR RefDate = EDATE ( [Date], 9 ) -- e.g. shifts Apr20-Mar21 => Jan-Dec 2021
        VAR FY = YEAR ( RefDate )
        RETURN
            ( FY - 1 ) & "/" & MOD ( FY, 100 ),
    "Org Financial Year",
        VAR RefDate = EDATE ( [Date], 6 ) -- e.g. shifts Jul20-Jun21 => Jan-Dec 2021
        VAR FY = YEAR ( RefDate )
        RETURN
            ( FY - 1 ) & "/" & MOD ( FY, 100 )
)

Does this work as intended?

[eisbaer99]

This is almost perfect to something I'm looking for.

The FW StartOfMonth are the exact dates we work to, any idea how I would convert this into M language so I can use it in PowerQuery?

[BINewbie1]

Hi Owen,

Yes that's fantastic. and that's for spotting the problem with Month Stort, probably saved me hours!

Adam

[OwenAuger]

Hi Adam,

Below is an example of what I would normally do, written as a DAX query (if I were creating the table in DAX).

The part you are actually asking about is step 3 but I have included an overall description.

In order to map dates into financial years/months/quarters, I would:

1. Create a "reference date" similar to what you had done with EDATE, but place the reference date in the later of the two years, e.g. dates in 2019/20 would have a reference date in 2020, as most orgs I've worked with seem to use the later year as an alternative standalone label for the financial year (it could equally be the earlier year if that is the convention).
2. Then take the year/quarter/month from the reference date.
3. Create the yyyy/yy label by subtracting 1 from the year derived in step 2 and construct a string. If years start in January, just use yyyy for the year itself.

Of course, this is just one method. I would also tend to prefer Power Query or further upstream to generate date tables, but the logic would be similar.

Have a look here for some further inspiration:

https://www.sqlbi.com/tools/dax-date-template/

EVALUATE
VAR StartDate = dt"2020-01-01"
VAR EndDate = dt"2023-12-31"
VAR UK_StartMonth = 4 -- UK Financial Year starts in April
VAR Org_StartMonth = 7 -- Org Financial Year starts in July
VAR UK_Offset =
    MOD ( 13 - UK_StartMonth, 12 )
VAR Org_Offset =
    MOD ( 13 - Org_StartMonth, 12 )
VAR BaseCalendar =
    CALENDAR ( StartDate, EndDate )
VAR Result =
    GENERATE (
        BaseCalendar,
        -- UK Financial Year Columns
        VAR UK_ReferenceDate =
            EDATE ( [Date], UK_Offset )  -- 9 in this case
        VAR Org_ReferenceDate =
            EDATE ( [Date], Org_Offset ) -- 6 in this case
        VAR UK_Year =
            YEAR ( UK_ReferenceDate )
        VAR UK_Quarter =
            QUARTER ( UK_ReferenceDate )
        VAR UK_Month =
        	MONTH ( UK_ReferenceDate )
        VAR UK_Year_Prev = UK_Year - 1
        VAR UK_Year_Label =
        	IF (
        		UK_StartMonth = 1,
        		CONVERT ( UK_Year, STRING ),
            	UK_Year_Prev & "/" & MOD ( UK_Year, 100 )
            )
        VAR UK_Quarter_Label = UK_Year_Label & " Q" & UK_Quarter
        -- Org Financial Year Columns
        VAR Org_Year =
            YEAR ( Org_ReferenceDate )
        VAR Org_Quarter =
            QUARTER ( Org_ReferenceDate )
        VAR Org_Month =
        	MONTH ( Org_ReferenceDate )
        VAR Org_Year_Prev = Org_Year - 1
        VAR Org_Year_Label =
        	IF (
        		Org_StartMonth = 1,
        		CONVERT ( Org_Year, STRING ),
        		Org_Year_Prev & "/" & MOD ( Org_Year, 100 )
        	)
        VAR Org_Quarter_Label = Org_Year_Label & " Q" & Org_Quarter
        RETURN
            ROW (
                "UK Financial Year", UK_Year,
                "UK Financial Quarter", UK_Quarter,
                "UK Financial Month", UK_Month,
                "UK Financial Year Label", UK_Year_Label,
                "UK Financial Quarter Label", UK_Quarter_Label,
                "Org Financial Year", Org_Year,
                "Org Financial Quarter", Org_Quarter,
                "Org Financial Month", Org_Month,
                "Org Financial Year Label", Org_Year_Label,
                "Org Financial Quarter Label", Org_Quarter_Label
            )
    )
RETURN
    Result

Regards

[BINewbie1]

Thanks Owen,

I think that will get me ther. Really appreciate the support.

I'm new to DAX and not a coder either, resonably competent with an excel formula though. Is there any chance you could offer some further support as you how I integrate this code with my existing code (the Calendar Table I already have), sorry should have posted that first really. Here is it, below. I think it just need to close off the Add Colummn commands and start a new section for this Evaluate command?

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2049, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "Month Num", MONTH ( [Date] ),
    "Week Num", WEEKNUM ( [Date] ),
    "Week Day", WEEKDAY ( [Date] ),
    "Day", DAY ( [DATE] ),
    "Month", FORMAT ( [Date], "Mmmm" ),
    "Day Name", FORMAT ( [Date], "dddd" ),
    "Quater Number", "Q" & QUARTER ( [Date] ),
    "Year Month",
        YEAR ( [Date] ) & " "
            & FORMAT ( [Date], "Mmmm" ),
    "Year Month Sort", YEAR ( [Date] ) & MONTH ( [Date] ),
    "Year Quarter",
        YEAR ( [Date] ) & " " & "Q"
            & QUARTER ( [Date] ),
    "Year Quarter Sort", YEAR ( [Date] ) & QUARTER ( [Date] ),
    "1 Month to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -1 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "3 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -3 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "6 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -6 )
                && [Date] <= TODAY (),
            TRUE ()
        ),
    "12 Months to Date",
        IF (
            EDATE ( [Date], 0 ) > EDATE ( TODAY (), -12 )
                && [Date] <= TODAY (),
            TRUE ()
        )
)

Thanks,

Adam