cancel
Showing results for
Did you mean:
Senior 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

Senior 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
Senior 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

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and 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.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 288 members 2,758 guests
Recent signins: