Filter - Financial Year Starts on October

DAX DAX Calculations
1

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.

Here’s my dax measure

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

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

Hi @ronald_balza

You would just have to change your fy start month to 10 instead of using 7 when you created your date table.

I hope this helps.
Keith

3

Hi @Keith, appreciate your quick response. I prefer not to make the adjustment directly on the date table, as this is specific to a particular measure. Is there a way to achieve this using DAX? Thanks! :blush:

4

not to my knowledge as your dax is based on your present fy date table. If i remember correctly there has been others that has asked that in the forum. Did you try searching or ask AI tool like (ChatGPT).?

thanks :slight_smile:

1 Like
5

If you need two different calendars and you don’t want to alter your date table to accommodate both you could create a second date table, a financial one, and use it when you need it. just follow @Keith’s note on creating that.

6

Answer: Hi @ronald_balza. Certainly, you can remove the original filter context and create a new list of date values beginning in October to be applied as a new filter context to the data model. However, this scenario prevents you from using the standard time intelligence functions. Also, the DAX code will become complex, hard to maintain and potentially render undesired results.

Let’s go through a different approach and provide the solution to your request.

  1. The Date table is marked as a date table and has a relationship with the HardwareRetailSales table. This setup is vital to simplify the use of time intelligence calculations.

Date Data
Date Data1922×1021 172 KB

  1. The Date table is built using a Power Query function. The function allows you to use an integer parameter to indicate the beginning of the financial year. For example, if you type 7, the calendar will create a new fiscal year for each value of July 1st. @Melissa has done a remarkable job in building the M script.

PQ Parameter
PQ Parameter1922×1022 293 KB

  1. As mentioned in the previous paragraph, the Fiscal Year column groups a range of dates from 01/07/20XX to 30/06/XX.

  2. Open the Power Query Editor and create a Fiscal Year V1 column beginning October 1st. This column will group the range of dates accordingly.

PQ Add FY
PQ Add FY1922×1022 366 KB

  1. Look at the matrices using the fiscal year and date columns. The matrix on your right uses the new Fiscal Year V1 column, providing a new grouping or hierarchy for the date values. It also uses the YTD Sales V1 measure, cumulating sales amount from the 1st of October.
Total Sales = SUM( HardwareRetailSales[Total] )

YTD Sales = 
CALCULATE (
    [Total Sales],
    DATESYTD (
        'Date'[Date],
        "2019-06-30"
    )
)

YTD Sales V1 = 
CALCULATE (
    [Total Sales],
    DATESYTD (
        'Date'[Date],
        "2019-09-30"
    )
)

YTD
YTD1922×1021 365 KB

Leveraging the time intelligence functions by creating additional fiscal year columns in the Date table and their corresponding measures will keep your DAX code simple.

The biggest takeaway from this example is that when creating a solution, data modelling comes first; DAX is the last step.

I hope this solution helps you. :blush:

Hardware Retail Sales.pbix (477.1 KB)

7

I think the OP stated altering the date table was not an option: