cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE Established Member
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
calerof Member
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:

Fiscal dates calculations.png

Finally, here is my pbix file:

pbix file

 

Hope it helps!

 

Cheers,

 

Fernando

 

5 REPLIES 5
calerof Member
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

 

Highlighted
DebbieE Established Member
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. 

calerof Member
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

 

DebbieE Established Member
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

 

 

calerof Member
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:

Fiscal dates calculations.png

Finally, here is my pbix file:

pbix file

 

Hope it helps!

 

Cheers,

 

Fernando

 

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 152 members 1,889 guests
Please welcome our newest community members: