Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
7 years ago
Solved

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 (Apri...
Need Help
  • calerof's avatar
    calerof
    7 years ago

    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

     

calerof's avatar
calerof
Impactful Individual
7 years ago

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's avatar
DebbieE
Community Champion
7 years ago

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's avatar
    calerof
    Impactful Individual
    7 years ago

    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