cancel
Showing results for
Did you mean:
Established Member

## Current Date flag for Financial Year

I have some DAX to set the current year flag

Current Year = IF (
YEAR ( 'dim Date'[date].[Date] ) = YEAR ( TODAY () ),
1,
0
)

But how do I set a flag for the current financial year (April to March not January to December)

Any help would be really appreciated
1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Current Date flag for Financial Year

Hi @DebbieE ,

Let's use the following data sample:

Sample Data

I created the Date Table with:

`FiscalCalendar = CALENDARAUTO(3)`

I marked it as Date Table.

Then I added the usual suspects as new columns, i.e. calendar year, month number, month, year-month number and year-month.

Then I added the Fiscal Year and Fiscal Period as follows:

```FY = VAR StartFY = 4
RETURN
IF(
MONTH(FiscalCalendar[Date]) >= StartFY,
YEAR(FiscalCalendar[Date]) + 1,
YEAR(FiscalCalendar[Date])
)```
```Fiscal Period = VAR StartFY = 4
RETURN
IF(
MONTH(FiscalCalendar[Date]) >= StartFY,
MONTH(FiscalCalendar[Date]) - (StartFY - 1),
MONTH(FiscalCalendar[Date]) + (12 - StartFY + 1)
)```

The credits for this calculated column are for Reza Rad @reza_rad with this blog:
Create a date dimension - fiscal columns

I then sorted month by Fiscal Period.

The sales figure is just a sum of the values.

The sales YTD value is with this measure:

```Sales YTD = CALCULATE(
Sales[Sales Amount],
DATESYTD(FiscalCalendar[Date], "31-03")
)```

Another sales YTD calculation is credit for @marcorusso in this blog:

Time intelligence issues in DAX for fiscal years

```Sales YTD 2 =
CALCULATE (
[Sales Amount],
VAR FirstFiscalMonth = 4
VAR LastDay =
MAX ( FiscalCalendar[Date] )
VAR LastMonth =
MONTH ( LastDay )
VAR LastYear =
YEAR ( LastDay )
- IF ( LastMonth < FirstFiscalMonth, 1 )
VAR FilterYtd =
DATESBETWEEN (
FiscalCalendar[Date],
DATE ( LastYear, FirstFiscalMonth, 1 ),
LastDay
)
RETURN
FilterYtd
)```

And here are the results:

Finally, here is my pbix file:

pbix file

Hope it helps!

Cheers,

Fernando

5 REPLIES 5
Member

## Re: Current Date flag for Financial Year

Hi @DebbieE ,

Try using this function while creating your Calendar Table:

`CALENDARAUTO`

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

Cheers,

Fernando

Established Member

## Re: Current Date flag for Financial Year

I dont understand how that will help me with the above query?

I use a dim date table created in the sql database.

Member

## Re: Current Date flag for Financial Year

Hi @DebbieE ,

You might want to create your own date table in your model with CALENDARAUTO and the fiscal year end of your choice. In this video Guy in a Cube shows how:

https://youtu.be/gyvhM5eiT0U

Hope it helps you.

Cheers,

Fernando

Highlighted
Established Member

## Re: Current Date flag for Financial Year

Ive have had a look at this video in the past

I understand I need a date dimension with Fiscal year in it but I dont underestand how to create a DAX query with a current date flag on Financial year

Member

## Re: Current Date flag for Financial Year

Hi @DebbieE ,

Let's use the following data sample:

Sample Data

I created the Date Table with:

`FiscalCalendar = CALENDARAUTO(3)`

I marked it as Date Table.

Then I added the usual suspects as new columns, i.e. calendar year, month number, month, year-month number and year-month.

Then I added the Fiscal Year and Fiscal Period as follows:

```FY = VAR StartFY = 4
RETURN
IF(
MONTH(FiscalCalendar[Date]) >= StartFY,
YEAR(FiscalCalendar[Date]) + 1,
YEAR(FiscalCalendar[Date])
)```
```Fiscal Period = VAR StartFY = 4
RETURN
IF(
MONTH(FiscalCalendar[Date]) >= StartFY,
MONTH(FiscalCalendar[Date]) - (StartFY - 1),
MONTH(FiscalCalendar[Date]) + (12 - StartFY + 1)
)```

The credits for this calculated column are for Reza Rad @reza_rad with this blog:
Create a date dimension - fiscal columns

I then sorted month by Fiscal Period.

The sales figure is just a sum of the values.

The sales YTD value is with this measure:

```Sales YTD = CALCULATE(
Sales[Sales Amount],
DATESYTD(FiscalCalendar[Date], "31-03")
)```

Another sales YTD calculation is credit for @marcorusso in this blog:

Time intelligence issues in DAX for fiscal years

```Sales YTD 2 =
CALCULATE (
[Sales Amount],
VAR FirstFiscalMonth = 4
VAR LastDay =
MAX ( FiscalCalendar[Date] )
VAR LastMonth =
MONTH ( LastDay )
VAR LastYear =
YEAR ( LastDay )
- IF ( LastMonth < FirstFiscalMonth, 1 )
VAR FilterYtd =
DATESBETWEEN (
FiscalCalendar[Date],
DATE ( LastYear, FirstFiscalMonth, 1 ),
LastDay
)
RETURN
FilterYtd
)```

And here are the results:

Finally, here is my pbix file:

pbix file

Hope it helps!

Cheers,

Fernando

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 41 members 1,044 guests
Recent signins: