none
Compute sessions conducted in H1 of FY

    Question

  • Hi,

    I use the following Calculated Field formula to compute the session conducted in each financial year (Financial year runs from April to March)

    =CALCULATE([Distinct sessions]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"3-31")
                                                           )
                           )

    This works very well.  I now want to break this result in H1 and H2 of each financial year i.e. H1 will be from April 1 of each financial year to March 31 of each financial year.

    What modification will I have to make to the formula above to compute Sessions conducted in H1 and Sessions conducted in H2.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, March 02, 2013 4:16 AM

Answers

  • Hi,

    Thank you for your advise.  I landed up following it and did the following:

    1. Created a calculated column (Financial Year Quarter) in the feedback table of the Power Pivot window

    =RELATED(Quarter_List[Quarter])

    I created a relationship between MonthNum column of the feedback table and monthnum column of the quarter list table

    2. For computing sessions in Q1, I then simply used the following calculated field formula

    =CALCULATE([Sessions conducted in FY],Feedback[Financial year quarter]="Q1")
    Thank you once again.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    • Marked as answer by Ashish Mathur Monday, March 04, 2013 8:49 AM
    Monday, March 04, 2013 8:49 AM

All replies

  • Hi Ashish -

    You could simplify the solution for many of these fiscal period measures you are working on by storing the periods as columns in your Date table instead of using DAX to calculate them at query-time.  With this date column approach, believe you could build a Fiscal Period hierarchy and simply drop that on a pivot axis and get what you're looking for with a simple distinctCount measure.

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Saturday, March 02, 2013 3:48 PM
  • Hi,

    Thank you for your comment.  I was actually trying to accomplish this by writing calculated field formula.  Anyways, I tried this calculated field formula

    =CALCULATE([Distinct sessions]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"03-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"9-30")
                                                           )
                           )

    But this seems to give the distinct count of sessions from April 1 of a financial year to September 30 of next financial year i.e. April 1, 2011 to September 30, 2012 whereas I want it from April 1, 2011 to September 30, 2011.  So I modified the calculated field formula to the following

    =CALCULATE([Distinct sessions]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"03-31")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"9-30")
                                                           )
                           )-
    CALCULATE([Distinct sessions]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,STARTOFYEAR(LASTDATE(Calendar1[DateKey]),"09-30")
                                                            ,endOFYEAR(LASTDATE(Calendar1[DateKey]),"9-30")
                                                           )
                           )
    Though the result is correct, the formula is lengthy. Is there a way to crunh the size of this formula wherein i can directly specify the date range as April 1 of every financial year to Setpember 30 of the same financial year.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Sunday, March 03, 2013 2:27 AM
  • Hi,

    OK so this is what I landed up doing

    I computed the following calculated fields

    Fiscal Year beg.

    =STARTOFYEAR(Calendar1[DateKey],"3-31")

    Fiscal Year end

    =ENDOFYEAR(Calendar1[DateKey],"3-31")

    Fiscal year mid

    =EOMONTH([Fiscal Year Beg],5)

    To compute Sessions in H1, I used the following calculated field formula

    =CALCULATE([Sessions conducted in FY]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,[Fiscal Year Beg]
                                                            ,[Fiscal Year mid]
                                                           )
                           )

    To compute Sessions in H2, I used the following calculated field formula

    =CALCULATE([Sessions conducted in FY]
                            ,DATESBETWEEN(Calendar1[DateKey]
                                                            ,[Fiscal Year mid]
                                                            ,[Fiscal Year end]
                                                           )
                           )

    I get all correct results.

    The only issue I am now facing is I am not getting any figure in the Grand total column for "Sessions in H1"

    What mistake am I committing?

    Pleas help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, March 04, 2013 2:37 AM
  • Hi,

    Thank you for your advise.  I landed up following it and did the following:

    1. Created a calculated column (Financial Year Quarter) in the feedback table of the Power Pivot window

    =RELATED(Quarter_List[Quarter])

    I created a relationship between MonthNum column of the feedback table and monthnum column of the quarter list table

    2. For computing sessions in Q1, I then simply used the following calculated field formula

    =CALCULATE([Sessions conducted in FY],Feedback[Financial year quarter]="Q1")
    Thank you once again.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    • Marked as answer by Ashish Mathur Monday, March 04, 2013 8:49 AM
    Monday, March 04, 2013 8:49 AM