Financial year and quarter formulas

[DK2006]

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

[schaema3]

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

[v-jiascu-msft]

Hi @DK2006,

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

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

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

[DK2006]

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