cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DK2006 Frequent Visitor
Frequent Visitor

Financial year and quarter formulas

Hi there

 

Could someone help with this one?

 

Our FY goes from 1st April to 31th March. What formula can I use to establish what FY and quarter does a particular date fall in?

 

Also is it possible if to have a formula to show if a particular project was runing during a particular Quarter or FY based on an open and closed date?

 

Kind regards

 

G

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Financial year and quarter formulas

Hi @DK2006,

 

We just need to add a column in the date table. Please check out the demo in the attachment. 

1. It's easy to find out the FY since we have the date table.

2. The formula could be like below.

Measure =
CALCULATE (
    CONCATENATEX ( DISTINCT ( 'Calendar'[FY] ), 'Calendar'[FY], "-" ),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( Table1[Start Date] )
            && 'Calendar'[Date] <= MIN ( Table1[Close Date] )
    )
)

Financial_year_and_quarter_formulas

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
DK2006 Frequent Visitor
Frequent Visitor

calculate year and quarter based on a date / period

Please could anyone help with this? 

 

Our FY goes from April to April. Could someone let me know which Power BI formula I need to use to establish when a date falls within a particular quarter or year?

 

How would I go about finding out if a particular project was runing at a particular quarter and FY (april to april again) based on the date it opened and the date it closed?

 

Many thans!!

 

G

 

 

 

 

Community Support Team
Community Support Team

Re: Financial year and quarter formulas

Hi @DK2006,

 

We just need to add a column in the date table. Please check out the demo in the attachment. 

1. It's easy to find out the FY since we have the date table.

2. The formula could be like below.

Measure =
CALCULATE (
    CONCATENATEX ( DISTINCT ( 'Calendar'[FY] ), 'Calendar'[FY], "-" ),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( Table1[Start Date] )
            && 'Calendar'[Date] <= MIN ( Table1[Close Date] )
    )
)

Financial_year_and_quarter_formulas

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Financial year and quarter formulas

Hi @DK2006,

 

Could you mark the proper answer as a solution, please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
schaema3 Frequent Visitor
Frequent Visitor

Re: Financial year and quarter formulas

You can use a separate column per quarter and use this formula:

 

QoQ-Q1 FY19 =
VAR __BASELINE_VALUE = SUM('Revenue'[Q4 FY18 Revenue])
VAR __VALUE_TO_COMPARE = SUM('Revenue'[Q1 FY19 Revenue])
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
(__VALUE_TO_COMPARE - __BASELINE_VALUE)
)

 

And create your own, company specific calendar where you specify which month falls into which FY. You can use something like this:

FY Quarter = SWITCH(TRUE(),'Financial'[FY Month]=1,"Q1",IF('Financial'[FY Month]=2,"Q1",IF('Financial'[FY Month]=3,"Q1",IF('Financial'[FY Month]=4,"Q2",IF('Financial'[FY Month]=5,"Q2",IF('Financial'[FY Month]=6,"Q2",IF('Financial'[FY Month]=7,"Q3",IF('Financial'[FY Month]=8,"Q3",if('Financial'[FY Month]=9,"Q3","Q4")))))))))