Forum Discussion

DK2006's avatar
DK2006
New Member
8 years ago
Solved

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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

  • DK2006's avatar
    DK2006
    New Member
    8 years ago

    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

     

     

     

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi DK2006,

     

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

     

    Best Regards,

    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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