Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
2 years ago
Solved

Dax - Filter - Financial Year

Hi everyone,

I have a financial year filter that starts in July. I have a measure and I want to modify the filter context for this calculation that the financial year filter will starts in October rather than July. Thanks in advance for help 🙂

Here’s my dax measure

    CALCULATE (
        [Total Sales (Journals)],
        FILTER ( Accounts, Accounts[Name] = "Fees - Permanent" )
    )

--name of financial year filter/slicer are FY24, FY23...

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ronaldbalza2023,

    Perhaps you can try to add a variable to get the current date, then you can use date function to extract the year and month part as condition to use in the Dax expressions to calculate the result in specific date ranges.

    For example: summary records from previous october to current year september.

    formula =
VAR currdate =
    MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        [Total Sales (Journals)],
        FILTER (
            ALLSELECTED ( 'SalesTable' ),
            [Date]
                >= DATE ( YEAR ( currdate ) - 1, 10, 1 )
                && [Date] < DATE ( YEAR ( currdate ), 10, 1 )
        ),
        FILTER ( Accounts, Accounts[Name] = "Fees - Permanent" )
    )

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ronaldbalza2023,

    Perhaps you can try to add a variable to get the current date, then you can use date function to extract the year and month part as condition to use in the Dax expressions to calculate the result in specific date ranges.

    For example: summary records from previous october to current year september.

    formula =
VAR currdate =
    MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        [Total Sales (Journals)],
        FILTER (
            ALLSELECTED ( 'SalesTable' ),
            [Date]
                >= DATE ( YEAR ( currdate ) - 1, 10, 1 )
                && [Date] < DATE ( YEAR ( currdate ), 10, 1 )
        ),
        FILTER ( Accounts, Accounts[Name] = "Fees - Permanent" )
    )

    Regards,

    Xiaoxin Sheng

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    I do not understand.  Why should there be a need to change the financial year?  Share some data to work with, explain the question and show the expected result.

    • ronaldbalza2023's avatar
      ronaldbalza2023
      Continued Contributor
      2 years ago

      Hi Ashish_Mathur, appreciate your response! I prefer not to make the adjustment directly on the date table, as this is applicable only on this measure. Sorry, coudn't share any workfiles on this. Regards, xx