locked
Determine number of sessions conducted in the previous period RRS feed

  • Question

  • Hi,

    Imagine simple dataset of a Soft Skills Training Company.  The data has only two columns - Date and Organised By.  Organised By carries the Name of the Company where the training was conducted.  If there were 10 participants from a certain Company, there will be 10 rows with the same Date and Company Name.

    The Financial Year of this Company runs from April to March.  There will be a slicer where the user will select any one Company.  Now imagine a Pivot Table, where Financial Years are dragged to the column labels and the user selects 1 Company in the slicer.  By using the following formula, one can easily compute the sessions conducted in every Financial Year.  So no problem so far.

    =DISTINCTCOUNT(Sessions[Date])

    I now want to compute the sessions conducted in the previous period.  Since, for the chosen Company, sessions may not be conducted in every FY, I cannot use the PREVIOUYEAR() function to compute the sessions conducted in the previous period.  To compute the sessions conducted in the previous period, I wrote the following measure but the return value is "Date on which the last session was conducted prior to the current FY".  I want the measure to return "Sessions conducted in the previous period".  Here's my failed attempt:

    =if(ISNUMBER([Sessions conducted]),CALCULATE(CALCULATETABLE(LASTNONBLANK(Sessions[Date],[Sessions conducted]),DATESBETWEEN('calendar'[Date],DATE(2007,4,1),MIN('calendar'[Date])-1))),BLANK())

    What mistake am I committing?  Please help.

    You may download the workbook from here.  I have shown my expected answer in the yellow row.

    Thank you for your help.


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

    Sunday, July 2, 2017 2:51 PM

Answers

  • Hi,

    I solved my problem.

    Here is the measure I wrote for "Last date of session conducted in previous period".  The result of this measure is the last date.

    =if(ISNUMBER([Sessions conducted]),CALCULATE(CALCULATETABLE(LASTNONBLANK(Sessions[Date],[Sessions conducted]),DATESBETWEEN('calendar'[Date],DATE(2007,4,1),MIN('calendar'[Date])-1))),BLANK())

    Here is the measure I wrote to determine the first date of the FY in which the previous session was conducted

    =if(HASONEVALUE('calendar'[Financial Year]),if(ISNUMBER([Sessions conducted]),if(MONTH([Last date of session conducted in previous period])<=3,IFERROR(DATE(YEAR([Last date of session conducted in previous period])-1,4,1),BLANK()),DATE(YEAR([Last date of session conducted in previous period]),4,1)),BLANK()),BLANK())

    Here is the measure I wrote to determine the last date of the FY in which the previous session was conducted

    =if(ISBLANK([First date of FY in which the last session was conducted]),BLANK(),if(HASONEVALUE('calendar'[Financial Year]),if(ISNUMBER([Sessions conducted]),EDATE([First date of FY in which the last session was conducted],12)-1,BLANK()),BLANK()))

    Here is the measure I wrote for computing the number of sessions conducted in the previous period

    =if(HASONEVALUE('calendar'[Financial Year]),if(ISBLANK([Last date of session conducted in previous period]),BLANK(),if(ISBLANK([Sessions conducted]),BLANK(),CALCULATE([Sessions conducted],DATESBETWEEN('calendar'[Date],[First date of FY in which the last session was conducted],[Last date of FY in which the last session was conducted])))),BLANK())

    Hope this helps.


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

    • Marked as answer by Ashish Mathur Saturday, July 8, 2017 3:08 AM
    Saturday, July 8, 2017 3:07 AM

All replies

  • Hi Ashish,

    Thanks for your question.

    You may create a calculated column called [FY] as below, and change the data type to whole number:

    [FY]:=LEFT('calendar'[Financial Year],4)
    Then you can create a measure called [Sessions conducted in PP] as below:
    [Sessions conducted in PP] :=
    IF (
        HASONEVALUE ( 'Sessions'[Organised by] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Sessions'[Date] ),
            FILTER ( ALL ( 'calendar' ), 'calenda'[FY] = MAX ( 'calenda'[FY] ) - 1 ),
            VALUES ( 'Sessions'[Organised by] )
        ),
        BLANK ()
    )
    

    Your expected result can not be achieved, we can only get the values as below:



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Tuesday, July 4, 2017 6:24 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With PQ generated fiscal calendar.
    Sessions per FY only.
    Added company "B".
    No attempt at "...previous period" since "Show values as ..." can be used.
    http://www.mediafire.com/file/gwpwom4k36h1k6f/07_04_17.xlsx

    Tuesday, July 4, 2017 7:01 PM
  • Hi,

    Thank you for replying.  That is not the answer I am expecting.  The number for FY 2015-16 should be 2.

    Please help.


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

    Saturday, July 8, 2017 2:25 AM
  • Thank you for replying.  I am looking at a PowerPivot solution.

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

    Saturday, July 8, 2017 2:26 AM
  • Hi,

    I solved my problem.

    Here is the measure I wrote for "Last date of session conducted in previous period".  The result of this measure is the last date.

    =if(ISNUMBER([Sessions conducted]),CALCULATE(CALCULATETABLE(LASTNONBLANK(Sessions[Date],[Sessions conducted]),DATESBETWEEN('calendar'[Date],DATE(2007,4,1),MIN('calendar'[Date])-1))),BLANK())

    Here is the measure I wrote to determine the first date of the FY in which the previous session was conducted

    =if(HASONEVALUE('calendar'[Financial Year]),if(ISNUMBER([Sessions conducted]),if(MONTH([Last date of session conducted in previous period])<=3,IFERROR(DATE(YEAR([Last date of session conducted in previous period])-1,4,1),BLANK()),DATE(YEAR([Last date of session conducted in previous period]),4,1)),BLANK()),BLANK())

    Here is the measure I wrote to determine the last date of the FY in which the previous session was conducted

    =if(ISBLANK([First date of FY in which the last session was conducted]),BLANK(),if(HASONEVALUE('calendar'[Financial Year]),if(ISNUMBER([Sessions conducted]),EDATE([First date of FY in which the last session was conducted],12)-1,BLANK()),BLANK()))

    Here is the measure I wrote for computing the number of sessions conducted in the previous period

    =if(HASONEVALUE('calendar'[Financial Year]),if(ISBLANK([Last date of session conducted in previous period]),BLANK(),if(ISBLANK([Sessions conducted]),BLANK(),CALCULATE([Sessions conducted],DATESBETWEEN('calendar'[Date],[First date of FY in which the last session was conducted],[Last date of FY in which the last session was conducted])))),BLANK())

    Hope this helps.


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

    • Marked as answer by Ashish Mathur Saturday, July 8, 2017 3:08 AM
    Saturday, July 8, 2017 3:07 AM