locked
Carry forward from previous months RRS feed

  • Question

  • Hi,

    My objective is to determine the Count of pending audits per month.  Pending audits per month will be the summation of:

    1. Audits initiated in the current month but not completed in the current month; and
    2. Audits initiated in previous months but not completed till the current month; and

    In this file (http://1drv.ms/1q7bNvz), please refer to the PowerPivot sheet.  The result for Jan 2014 is 6 - This is the result for 1 above.  The result should actually be 14.  There were 8 (1 of November and 7 of December) audits in the period from Oct-Dec 2013 which were not completed till January 2014.

    What change would I have to carry out in the DAX formula to generated the correct result i.e. 8+6.

    I believe I am committing some mistake in the ALL part my formula.

    Thank you for your help.


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

    Monday, September 8, 2014 2:50 PM

Answers

  • Hi,

    Thank you for replying.  This works.  I modified your formula to this

    =CALCULATE (COUNTROWS ( 'Audit data' ),FILTER(ALL ( 'Audit data' ),'Audit data'[Audit Firm Report Received Date]<= ENDOFMONTH(  VALUES ( Calendar[Date]))&& 'Audit data'[Final Closure Date]> ENDOFMONTH (VALUES(Calendar[Date]))&& 'Audit data'[Final Closure Date] <> BLANK ()))

    and got the right answer for Jan 2014 i.e. 14.  I am yet to check the result for the other months.

    I have one doubt though.  What is the purpose of the VALUES() function here.  If I remove both the VALUES() function, I get an answer of 73 (for all months).  So it is the VALUES function which is making it work.  As far as I know form this link, the VALUES function return a single column of unique dates from the Date column of the calendar Table.  Since the entries in that column are anyways unique, what is the purpose of the VALUES() function?

    Thank you for all your helps so far.


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

    Tuesday, September 9, 2014 1:29 PM
  • You'll need to use the AVERAGEX() function.

    AVERAGEX( 
        FILTER( ALL( <fact table> )
            , <opened date> <= MAX( <date> )
                && <closed date> > MAX( <date> )
        )
        , MAX( <date> ) - <opened date>
    )

    AVERAGEX() iterates through the table you provide in the first argument and performs the expression you provide as the second argument in that row's context, returning the average of all rows.

    The FILTER() will provide a table of all audits with an open date before or on the last day of the month in context and with a close date after the last day of the month in context.

    Tuesday, September 23, 2014 9:15 PM
  • Ashish, the sample data you have provided does not have any audits with [Audit Firm Report Received Date] in November 2013 or December 2013 that have a [Final Closure Date] in January 2014, which is what you've been saying (not completed til current month), and repeat above.

    I think I see the misunderstanding though, as later in your most recent post, you indicate you're looking for [Final Closure Date] AFTER the current month. We can work with this.

    First of all, with the new understanding of the requirements, the second inactive relationship is unnecessary - this means your overall schema need not be altered at all.

    The new complete set of measures:

    1.

    InitiatedCurrentMonth :=
    COUNTROWS ( 'Audit data' )

    2.

    InitiatedCompletedCurrentMonth :=
    CALCULATE (
        [InitiatedCurrentMonth],
        FILTER (
            'Audit data',
            MONTH ( 'Audit data'[Final Closure Date] )
                = MAX ( Calendar[Order] )
        )
    )

    3.

    InitiatedPriorCompletedSubsequent :=
    CALCULATE (
        COUNTROWS ( 'Audit data' ),
        FILTER (
            ALL ( 'Audit data' ),
            'Audit data'[Audit Firm Report Received Date]
                < STARTOFMONTH ( VALUES ( Calendar[Date] ) )
                && 'Audit data'[Final Closure Date]
                    > ENDOFMONTH ( VALUES ( Calendar[Date] ) )
                && 'Audit data'[Final Closure Date] <> BLANK ()
        )
    )
    

    We can combine these as follows:

    TotalPending :=
     ( [InitiatedCurrentMonth] - [InitiatedCompletedCurrentMonth] )
        + [InitiatedPriorCompletedSubsequent]
    

    Let us know if these meet your needs.

    • Marked as answer by Ashish Mathur Thursday, September 11, 2014 3:37 AM
    Tuesday, September 9, 2014 1:04 PM
  • That's a very good modification to the formula. Based on my original understanding, breaking it up seemed a necessary step, but you're right, that with the better understanding it can be combined into the formula you've provided.

    The VALUES() function actually returns a table, not a column (but if it returns a one column table, it can be implicitly converted to a column, and if it returns a one column one row table, it can be implicitly converted to a scalar.

    What it does is returns this table based on the row/column filter context, so it essentially reaches outside the scope of the FILTER() (which is working on ALL('Audit Data') - this implicitly includes all of the calendar) and reads the filter context based on the row and column filters (which means it respects the month and year based on the cell of the pivot table). So VALUES(Calendar[Date]) returns only the dates in the current year-month. If we do not use values, we will calculate across all dates, not just the dates of the cell context.

    This post has an in-depth discussion of the way DAX is looking at tables and relationships. It should help explain things better than I have here.

    If you have further questions, feel free to follow up.

    Also, as a side note, DAX Formatter can be very helpful for the readability of DAX formulas.

    Tuesday, September 9, 2014 2:20 PM

All replies

  • Ashish, please clarify on the date fields included in your model. You have [Onsite Date], [Audit Firm Report Received Date], and [Final Closure Date]. I am not sure which ones to be using. I had started looking at [Audit Firm Report Received Date] as the initiated date, but this does not add up based on the example you made in your post. Then I looked at [Onsite Date] as initiated date, but this also does not align with the example you provided, as I can find [Onsite Date]s with [Audit Firm Report Received Date]s and/or [Final Closure Date]s in January from September, and your example indicates that nothing initiated in September 2013 is completed in January 2014.

    Please let us know which dates correspond to the criteria you have laid out.

    Also, it looks like you may have omitted a row from your enumeration of criteria, as your line 2 ends in "and"

    Monday, September 8, 2014 4:40 PM
  • Hi,

    Thank you for replying.  You have to look at [Audit Firm Report Received Date] and [Final Closure Date].  So in January 2014, I want the answer as 14 which is broken up as 6+8.  6 are the audits which have [Audit Firm Report Received Date] of Jan 2014 and [Final Closure Date] after January 2014.  8 are audits which have [Audit Firm Report Received Date] of November (1) and December (7) and [Final Closure Date] after January 2014.  The same needs to be done for other months as well.  Please ignore the [Onsite Date] column. I have not omitted any row.  The and after the second criteria should not be there - there are only two conditions as laid out above.


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

    Monday, September 8, 2014 10:53 PM
  • Ashish,

    I think you may have uploaded a different copy of the workbook to Onedrive than you are looking at, because the example you are speaking of does not match what I can see in the workbook. Nonetheless, we've nailed the necessary logic.

    I broke this problem down into a series of components:

    1. Audits with [Audit Firm Report Received Date] in the current month
    2. Audits with both [Audit Firm Report Received Date] and [Final Closure Date] in the current month - these are to be ignored based on the logic discussed
    3. Audits with [Final Closure Date] in the current month

    From these components we can perform some simple math to get to the end numbers we need. I'll make a note that based on the process I've followed, those audits with [Audit Firm Report Received Date] in the current month and with a blank [Final Closure Date] are counted toward pending audits for the month they are created in. If this behavior is not desired, we can add an additional filter to eliminate them

    To make these measures work, I have added an additional relationship from 'Audit data'[Final Closure Date] to Calendar[Date]. This relationship is inactive.

    The measures:

    1. 

    InitiatedCurrentMonth :=
    COUNTROWS ( 'Audit data' )

    2.

    InitiatedCompletedCurrentMonth :=
    CALCULATE (
        [InitiatedCurrentMonth],
        FILTER (
            'Audit data',
            MONTH ( 'Audit data'[Final Closure Date] )
                = MAX ( Calendar[Order] )
        )
    )

    3. 

    CompletedCurrentMonth :=
    CALCULATE (
        COUNTROWS ( 'Audit data' ),
        USERELATIONSHIP ( 'Audit data'[Final Closure Date], Calendar[Date] ),
        'Audit data'[Final Closure Date] <> BLANK ()
    )

    These measures each calculate a count. We can combine these counts as follows to get what we need:

    TotalPending :=
     ( [InitiatedCurrentMonth] - [InitiatedCompletedCurrentMonth] )
        + ( [CompletedCurrentMonth] - [InitiatedCompletedCurrentMonth] )

    This gives all of the audits initiated in the current month with a close date which does not exist in the current month ( [InitiatedCurrentMonth] - [InitiatedCompletedCurrentMonth] ) and all of the audits completed in the current month with an initiation date not in the current month ( [CompletedCurrentMonth] - [InitiatedCompletedCurrentMonth] ). Note that this includes audits with blanks for [Final Closure Date] in the first category.

    If you have any questions about these measures, please feel free to follow up.

    Tuesday, September 9, 2014 3:30 AM
  • Hi,

    Thank you for your effort in helping me.  Please ignore the Pivot Table worksheet and Onsite Date column of the Audit Data worksheet.

    Let me explain my problem again.  My objective is to determine the audits pending month wise.  So in January 2014, I want to determine the total number of pending audits.  Pending audits in any month can be determined as follows:

    1. Audits initiated (Audit Firm Report Received Date) in the current month but not completed (Final Closure Date) in the current month; and

    2. Audits initiated (Audit Firm Report Received Date) in previous months but not completed (Final Closure Date) till the current month

    So for January 2014, the way to determine this would be:

    1. Filter the Audit Firm Report Received Date column on all dates up to (including) Jan 2014; and

    2. Filter the Final Closure Date column on all dates after Jan 2014

    The resultant rows will be 14 i.e. audits pending in the month of January (these audits were all initiated till January 2014 but were completed only after January 2014)

    I want the answer in cell C3 of the PowerPivot worksheet to be 14.  The same logic should apply for the other months as well.

    My DAX formula is returning 6 as the answer in cell C3.  This is the number of audits which were initiated in Jan 2014 but were not competed in Jan 2014.  I just want to add 8 to this number which comprises 1 pending audit of November 2013 and 7 of December 2013.  The November audit was completed in Feb 2014 and the December audits were completed in Feb/Mar 2014.

    Here is the file link.

    Thank you.


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

    Tuesday, September 9, 2014 8:42 AM
  • Ashish, the sample data you have provided does not have any audits with [Audit Firm Report Received Date] in November 2013 or December 2013 that have a [Final Closure Date] in January 2014, which is what you've been saying (not completed til current month), and repeat above.

    I think I see the misunderstanding though, as later in your most recent post, you indicate you're looking for [Final Closure Date] AFTER the current month. We can work with this.

    First of all, with the new understanding of the requirements, the second inactive relationship is unnecessary - this means your overall schema need not be altered at all.

    The new complete set of measures:

    1.

    InitiatedCurrentMonth :=
    COUNTROWS ( 'Audit data' )

    2.

    InitiatedCompletedCurrentMonth :=
    CALCULATE (
        [InitiatedCurrentMonth],
        FILTER (
            'Audit data',
            MONTH ( 'Audit data'[Final Closure Date] )
                = MAX ( Calendar[Order] )
        )
    )

    3.

    InitiatedPriorCompletedSubsequent :=
    CALCULATE (
        COUNTROWS ( 'Audit data' ),
        FILTER (
            ALL ( 'Audit data' ),
            'Audit data'[Audit Firm Report Received Date]
                < STARTOFMONTH ( VALUES ( Calendar[Date] ) )
                && 'Audit data'[Final Closure Date]
                    > ENDOFMONTH ( VALUES ( Calendar[Date] ) )
                && 'Audit data'[Final Closure Date] <> BLANK ()
        )
    )
    

    We can combine these as follows:

    TotalPending :=
     ( [InitiatedCurrentMonth] - [InitiatedCompletedCurrentMonth] )
        + [InitiatedPriorCompletedSubsequent]
    

    Let us know if these meet your needs.

    • Marked as answer by Ashish Mathur Thursday, September 11, 2014 3:37 AM
    Tuesday, September 9, 2014 1:04 PM
  • Hi,

    Thank you for replying.  This works.  I modified your formula to this

    =CALCULATE (COUNTROWS ( 'Audit data' ),FILTER(ALL ( 'Audit data' ),'Audit data'[Audit Firm Report Received Date]<= ENDOFMONTH(  VALUES ( Calendar[Date]))&& 'Audit data'[Final Closure Date]> ENDOFMONTH (VALUES(Calendar[Date]))&& 'Audit data'[Final Closure Date] <> BLANK ()))

    and got the right answer for Jan 2014 i.e. 14.  I am yet to check the result for the other months.

    I have one doubt though.  What is the purpose of the VALUES() function here.  If I remove both the VALUES() function, I get an answer of 73 (for all months).  So it is the VALUES function which is making it work.  As far as I know form this link, the VALUES function return a single column of unique dates from the Date column of the calendar Table.  Since the entries in that column are anyways unique, what is the purpose of the VALUES() function?

    Thank you for all your helps so far.


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

    Tuesday, September 9, 2014 1:29 PM
  • That's a very good modification to the formula. Based on my original understanding, breaking it up seemed a necessary step, but you're right, that with the better understanding it can be combined into the formula you've provided.

    The VALUES() function actually returns a table, not a column (but if it returns a one column table, it can be implicitly converted to a column, and if it returns a one column one row table, it can be implicitly converted to a scalar.

    What it does is returns this table based on the row/column filter context, so it essentially reaches outside the scope of the FILTER() (which is working on ALL('Audit Data') - this implicitly includes all of the calendar) and reads the filter context based on the row and column filters (which means it respects the month and year based on the cell of the pivot table). So VALUES(Calendar[Date]) returns only the dates in the current year-month. If we do not use values, we will calculate across all dates, not just the dates of the cell context.

    This post has an in-depth discussion of the way DAX is looking at tables and relationships. It should help explain things better than I have here.

    If you have further questions, feel free to follow up.

    Also, as a side note, DAX Formatter can be very helpful for the readability of DAX formulas.

    Tuesday, September 9, 2014 2:20 PM
  • Hi,

    Thank you for all your help so far.  I am just stuck up with one last thing.  In the PowerPivot worksheet of this file, I want to get the average number of days in the subtotal cells i.e. D4, D19, D34 etc.

    Could you please help.


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

    Friday, September 12, 2014 2:53 AM
  • Any help please.

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

    Wednesday, September 17, 2014 12:17 AM
  • You'll need to use the AVERAGEX() function.

    AVERAGEX( 
        FILTER( ALL( <fact table> )
            , <opened date> <= MAX( <date> )
                && <closed date> > MAX( <date> )
        )
        , MAX( <date> ) - <opened date>
    )

    AVERAGEX() iterates through the table you provide in the first argument and performs the expression you provide as the second argument in that row's context, returning the average of all rows.

    The FILTER() will provide a table of all audits with an open date before or on the last day of the month in context and with a close date after the last day of the month in context.

    Tuesday, September 23, 2014 9:15 PM