Forum Discussion

Billgates's avatar
Billgates
Frequent Visitor
2 years ago
Solved

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 Occur...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.