cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dynamic Custom Fiscal Year

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.

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Frequent Visitor

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

Frequent Visitor

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

Frequent Visitor

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)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors