Current Date flag for Financial Year

[DebbieE]

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

[calerof]

Hi @DebbieE ,

Try using this function while creating your Calendar Table:

CALENDARAUTO

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

Cheers,

Fernando

[DebbieE]

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

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

[calerof]

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

[DebbieE]

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

[calerof · Accepted solution]

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