topic Re: Dynamic Custom Fiscal Year in DAX Commands and Tips

Dynamic Custom Fiscal Year

Billgates — Mon, 10 Jun 2024 18:18:33 GMT

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.

Re: Dynamic Custom Fiscal Year

ArtisanAmbrosia — Mon, 10 Jun 2024 18:33:30 GMT

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

Re: Dynamic Custom Fiscal Year

Billgates — Mon, 10 Jun 2024 18:50:17 GMT

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

Re: Dynamic Custom Fiscal Year

ArtisanAmbrosia — Mon, 10 Jun 2024 19:19:06 GMT

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)

Re: Dynamic Custom Fiscal Year

Anonymous — Tue, 11 Jun 2024 06:12:10 GMT

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.