Dynamic Custom Fiscal Year

[Billgates]

Fact Table - Column 1, Date of Occurence

Date Dimension Table - Date, Calendar Year, Fiscal Year, Fiscal Period, Fiscal Week

I've to write a DAX measure that counts Column 1 where the Date of Occurence is in the last Fiscal Year. 

[v-jiewu-msft · Accepted solution]

Hi @Billgates ,

Based on my testing, please try the following methods again:

1.Create the simple tables.

2.Create the relationship between the two tables.

3.Create the new measure to calculate the last fiscal year counts.

CountLastFiscalYear = 
VAR CurrentFiscalYear = MAX('Table'[Fiscal year]) - 1
RETURN
CALCULATE(
    COUNTROWS('Fact Table'),
    FILTER(
        'Table',
        'Table'[Fiscal year] = CurrentFiscalYear
    )
)

4.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArtisanAmbrosia]

I find using the below linked table best for Fiscal Year reporting. The table has a Fiscal Year offset and various Fiscal Year fields when you input the month number of your first fiscal month. 

https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

[Billgates]

Thank you, I have my date table I just want a DAX measure to meet my requirements

[ArtisanAmbrosia]

Well, you may need to improve your date table...

With a relationship between 'Fact Table'[Date of Occurence] and 'Date'[Date] 
You could hardcode the measure, which isn't ideal for when next FY comes:
Last FY Count = CALCULATE(COUNT('Fact'[Column 1]), 'Date Table'[Fiscal Year] = "2023")

Or you could use variables for something like:
Last FY Count = VAR FYStart = [Date] >= "5/1/2023"

VAR FYEnd = [Date] <= "4/30/2024"

VAR CountFact = Count('Fact'[Column 1]

RETURN

CALCULATE(CountFact), [Date of Occurence] >= FYStart && [Date of Occurence] <= FYEnd)