none
Determine whether outlet was visited in next month or not

    Question

  • Hi,

    I have a simple two column table - Sales date and Outlet ID.  The tab name is Sales data.  I want to write a calculated column formula in the Power Pivot window which will tell me:

    "Was the outlet visited in the next month?"

    So let's say that a certain outlet was visited on 12 March 2014. I want to determine whether the same outlet was visited next month i.e. between 1 April 2014 to 30 April 2014?

    I want a TRUE in the 12 March 2014 row if the outlet was visited between 1 April 2014 to 30 April 2014 and FALSE if it was not visited between that period.

    Please help.


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

    Thursday, May 29, 2014 11:23 PM

Answers

  • You could try using that same formula within a formula like

    COUNTX(ADDCOLUMNS(Sales, "NewColumn", IF(
    CALCULATE (
        COUNTROWS ( Sales ),
        FILTER ( Sales, 
                            [Outlet ID] = EARLIER ( [Outlet ID] )&&
                            [Date]>= EOMONTH ( EARLIER([Date]), 0 ) + 1&&
                            [Date] <= EOMONTH ( EARLIER([Date]), 1 )
        )
    )>0,"TRUE", "FALSE"
    ) ), [NewColumn])

    OR! I'd probably go with a calculated field using COUNTX and SUMMARIZE so that it can do that iteration under less rows (something like, using summarize to grab just the month and not the date to make the table smaller in height).

    There are other ways of doing this with a dates table but If I had a timeline slicer or any date slicers at all, I'd probably change that formula to take in consideration only the current selection.

    Hope this helps!

    Monday, June 02, 2014 9:13 AM

All replies

  • The key to this is understanding the row filter context in the PowerPivot window - i.e. there isn't one!

    Try this:

    =IF(
    CALCULATE (
        COUNTROWS ( Sales ),
        FILTER ( Sales, 
                            [Outlet ID] = EARLIER ( [Outlet ID] )&&
                            [Date]>= EOMONTH ( EARLIER([Date]), 0 ) + 1&&
                            [Date] <= EOMONTH ( EARLIER([Date]), 1 )
        )
    )>0,"TRUE", "FALSE"
    )


    Jacob | Please mark helpful posts and answers



    Sunday, June 01, 2014 1:43 PM
  • Hi,

    Thank you for replying.  Is there a way to directly write a calculated field formula to compute - "How many outlets were not visited next month".  My thinking earlier was that once I get the TRUE/FALSE (as your formula now gives me), I will write a calculated field formula to count the FALSE's.

    However, I am now taking it one step higher where I would like to get the count directly via a calculated field formula (by passing a calculated column formula).

    Thank you for your help.


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

    Monday, June 02, 2014 3:46 AM
  • You could try using that same formula within a formula like

    COUNTX(ADDCOLUMNS(Sales, "NewColumn", IF(
    CALCULATE (
        COUNTROWS ( Sales ),
        FILTER ( Sales, 
                            [Outlet ID] = EARLIER ( [Outlet ID] )&&
                            [Date]>= EOMONTH ( EARLIER([Date]), 0 ) + 1&&
                            [Date] <= EOMONTH ( EARLIER([Date]), 1 )
        )
    )>0,"TRUE", "FALSE"
    ) ), [NewColumn])

    OR! I'd probably go with a calculated field using COUNTX and SUMMARIZE so that it can do that iteration under less rows (something like, using summarize to grab just the month and not the date to make the table smaller in height).

    There are other ways of doing this with a dates table but If I had a timeline slicer or any date slicers at all, I'd probably change that formula to take in consideration only the current selection.

    Hope this helps!

    Monday, June 02, 2014 9:13 AM
  • Thank you.

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

    Saturday, June 07, 2014 7:13 AM