cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## DAX Fiscal Period Per Company

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

1 ACCEPTED SOLUTION
Solution Sage

Hi @Monty0

build this model:

```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] ) )
)
)
)```

Proud to be a Datanaut!

4 REPLIES 4
Solution Sage

Hi @Monty0

are you able to post a sample dataset?

Proud to be a Datanaut!

Frequent Visitor

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

M

Solution Sage

Hi @Monty0

build this model:

```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] ) )
)
)
)```

Proud to be a Datanaut!

Frequent Visitor

Thank you LivioLanzo, outstanding!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors