Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Monty0
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

 

Thanks in advance 🙂

1 ACCEPTED SOLUTION

Hi @Monty0

 

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

 

build this model:

 

Capture.PNGCapture1.PNG

 

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

Capture2.PNG

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

Hi @Monty0

 

are you able to post a sample dataset?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi LivioLanzo

 

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

 

M

Hi @Monty0

 

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

 

build this model:

 

Capture.PNGCapture1.PNG

 

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

Capture2.PNG

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Thank you LivioLanzo, outstanding!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.