Forum Discussion
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.
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
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
Thank you, I have my date table I just want a DAX measure to meet my requirements
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)
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.
