DAX Measure to show current fiscal year

[FatBlackCat30]

Hi all,

I am trying to create a DAX measure that I could add to a matrix visual that will return the current fiscal year

this has been tricky because the new FY starts on 7/1 and ends on 6/30. 

my data looks like this:

Date		users
6/20/2020		150
6/21/2020		160
6/22/2020		160
6/23/2020		180
6/24/2020		183
6/25/2020		548
6/26/2020		438
6/27/2020		497
6/28/2020		557
6/29/2020		616
6/30/2020		676
7/1/2020		735
7/2/2020		794
7/3/2020		854

with a dax measure the outcome would look like this:

Date		FY		users
6/20/2020		20		150
6/21/2020		20		160
6/22/2020		20		160
6/23/2020		20		180
6/24/2020		20		183
6/25/2020		20		548
6/26/2020		20		438
6/27/2020		20		497
6/28/2020		20		557
6/29/2020		20		616
6/30/2020		20		676
7/1/2020		21		735
7/2/2020		21		794
7/3/2020		21		854

What would be a good approach to accomplish this? 

[Nathaniel_C · Accepted solution]

Hi @FatBlackCat30 ,
Try this measure for the results below:

FY = 
VAR ThisYear = MAX('Table'[Date])
Var ThisYear2 = YEAR(ThisYear)
Var ThisMonth = MAX('Table'[Date])
Var ThisMonth2 = MONTH(ThisMonth)

Var _calc = IF(ThisMonth2<=6,ThisYear2 -2000,ThisYear2-1999)

Return _calc

or more compressed:

FY 1 = 

IF(MONTH(MAX('Table'[Date]))<=6, YEAR(MAX('Table'[Date]))-2000,YEAR(MAX('Table'[Date]))-1999)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[caitlynnguyen08]

I am trying to create a fiscal year measure out of these Fields. Can someone help me write a measure using these? I cannot add columns or tables since this is a PBI dataset. 

Thanks!

[Ashish_Mathur]

Hi,

You will need to write a calculated column formula for this in your Calendar Table.

[Nathaniel_C]

Hi @FatBlackCat30 ,
If you would like to use this as a column for a slicer, just remove the MAX() from any of the DAX formulas that I gave you. Like below

Use as col = 

IF(MONTH('Table'[Date])<=6, YEAR('Table'[Date])-2000,YEAR('Table'[Date])-1999)

  You need to use some type of aggregator when you use a measure, but with a table, DAX knows which row it is on, so they are not in use. This is known as Row Context. 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[CNENFRNL]

Hi, @FatBlackCat30 , you might want to try this measure

FY = 
VAR __date = MAX ( Table1[Date] )
VAR __cy = YEAR ( __date )
RETURN
    RIGHT ( __cy + ( __date > DATE ( __cy, 6, 30 ) ) - 1, 2 )

[Nathaniel_C · Accepted solution]

Hi @FatBlackCat30 ,
Try this measure for the results below:

FY = 
VAR ThisYear = MAX('Table'[Date])
Var ThisYear2 = YEAR(ThisYear)
Var ThisMonth = MAX('Table'[Date])
Var ThisMonth2 = MONTH(ThisMonth)

Var _calc = IF(ThisMonth2<=6,ThisYear2 -2000,ThisYear2-1999)

Return _calc

or more compressed:

FY 1 = 

IF(MONTH(MAX('Table'[Date]))<=6, YEAR(MAX('Table'[Date]))-2000,YEAR(MAX('Table'[Date]))-1999)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @FatBlackCat30 ,
Upon thinking about it, this might be a better function.

FY 2 = 

IF(MONTH(MAX('Table'[Date]))<=6, RIGHT(YEAR(MAX('Table'[Date])),2), RIGHT(YEAR(MAX('Table'[Date]))+1,2))//This trims off the last two digits of the year

They all work, as seen below:

I like adding 1 to get a later year, rather than subtracting 1 less to get a later year.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel