cancel
Showing results for
Did you mean:
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

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

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

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

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

## Re: Financial year and quarter formulas

Hi @DK2006,

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.
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")))))))))