Forum Discussion

RichOB's avatar
RichOB
Post Partisan
1 year ago
Solved

DAX for Financial Year column

Hi, I'm looking to get the DAX just for the financial year, please.   I have individual columns for each of the below, what would be the DAX to get an individual column for the financial year?    ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Hi RichOB

    Here is a blog on generating a basic calendar table: https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm

     

    The code you want is:

    "Financial Year" = IF(

    [Date] >= DATE(Year([Date]), 4, 1),

    Year([Date]) & "/" & RIGHT(Year([Date]) +1,2),

    Year([Date])-1 &"/" & RIGHT(Year([Date]),2)

    )

     

     

    "Financial Quarter" =SWITCH(

    TRUE(),

    MONTH([Date]) IN {4,5,6},"Qtr 1",

    MONTH([Date]) IN {7,8,9},"Qtr 2",

    MONTH([Date]) IN {10,11,12},"Qtr 3",

    "Qtr 4"

    )

     

    // financial month within quarter

    "Financial Month"  = IF(

    MONTH([Date]) >= 4,

    MONTH([Date]) - 3,

    MONTH([Date]) + 9

    ),

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

Hi RichOB

Here is a blog on generating a basic calendar table: https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm

 

The code you want is:

"Financial Year" = IF(

[Date] >= DATE(Year([Date]), 4, 1),

Year([Date]) & "/" & RIGHT(Year([Date]) +1,2),

Year([Date])-1 &"/" & RIGHT(Year([Date]),2)

)

 

 

"Financial Quarter" =SWITCH(

TRUE(),

MONTH([Date]) IN {4,5,6},"Qtr 1",

MONTH([Date]) IN {7,8,9},"Qtr 2",

MONTH([Date]) IN {10,11,12},"Qtr 3",

"Qtr 4"

)

 

// financial month within quarter

"Financial Month"  = IF(

MONTH([Date]) >= 4,

MONTH([Date]) - 3,

MONTH([Date]) + 9

),

RichOB's avatar
RichOB
Post Partisan
1 year ago

This was very helpful, thank you!