DAX Fiscal Period Per Company

[Monty0]

Hi

I am trying to calculate the fiscal YTD amount for all previous years for multiple companies. I need the fiscal period to be dynamic as each company has a different fiscal period. At the moment I can get it to work using the following code but the fiscal year end is not dynamic (is is 03-31 for all companies for which the calculation is applied)

Turnover FY:=
    CALCULATE (
        [Turnover Amount],
        DATESYTD ( 'AccountingPeriod'[Date], "03-31" )
    )

Anyone solve this before or could guide me to a solution?

Here is an example data set, in it I have company A and B with fiscal periods ending 01/10 and 01/06. Column 'Turnover FY' is the calculation I am trying to create

Fiscal Period Sample

Thanks in advance 🙂

[LivioLanzo]

Hi @Monty0

are you able to post a sample dataset?

[Monty0]

Hi LivioLanzo

Thanks for having a look. I attached an example. Let me know if anything is unclear

M

[LivioLanzo · Accepted solution]

Hi @Monty0

you can download the file: https://1drv.ms/u/s!AiiWkkwHZChHj0ItKq-ShSK2gIqr

build this model:

Then add this measure:

Measure =
IF (
    HASONEVALUE ( 'Calendar'[Month] ),
    SUMX (
        Companies,
        VAR FYEDate =
            DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), Companies[FYE Month], Companies[FYE Day] )
        VAR PeriodBegin =
            IF (
                MAX ( 'Calendar'[Date] ) > FYEDate,
                FYEDate + 1,
                EDATE ( FYEDate, -12 ) + 1
            )
        RETURN
            CALCULATE (
                SUM ( Turnover[Turnover Amount] ),
                DATESBETWEEN ( 'Calendar'[Date], PeriodBegin, MAX ( 'Calendar'[Date] ) )
            )
    )
)

[Monty0]

Thank you LivioLanzo, outstanding!