locked
[LY Sales] function throws error when selecting periods in different years RRS feed

  • Question

  • I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) 

    The DAX code for calculating LY Sales is:

    =
    CALCULATE (
        [Sales $],
        FILTER (
            ALL ( dCalendar ),
            dCalendar[IRIYearNumber]
                VALUES ( dCalendar[IRIYearNumber] ) - 1
                && CONTAINS (
                    VALUES ( dCalendar[PeriodNumber] ),
                    dCalendar[PeriodNumber], dCalendar[PeriodNumber]
                )
        )
    )



    Code for [Sales $] used above:

    =
    CALCULATE (
        SUM ( POS_IRI[Dollar Sales] )
            SUM ( POS_Retailer[SalesDollars] )
            SUM ( POS_Retailer2[SalesDollars] )
            SUM ( POS_Retailer3[SalesDollars] )
            SUM ( POS_Retailer4[SalesDollars] )
            SUM ( POS_Retailer5[SalesDollars] ),
        dProducts[Filter] = 1,
        dCustomers[Filter] = 1
    )

    The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report. 

    The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;

    ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected. 


    Thursday, May 21, 2015 7:46 PM

Answers

  • Hi g.hardwick,

    You could add another column to your date table that contains IRIYearNumber and PeriodNumber values combined in YYYYMM format. You can do this by creating a Calculated Column with the following DAX and calling it 'YearPeriodNumber'. Be sure to set the data type to 'Whole Number':

    =dCalendar[IRIYearNumber] & RIGHT("00" & dCalendar[PeriodNumber], 2)

    After doing this, you can try the following DAX formula:

    LY Sales 2 :=
    CALCULATE(
      [Sales $],
      FILTER(
        ALL(dCalendar),
        dCalendar[YearPeriodNumber] >= MIN (dCalendar[YearPeriodNumber]) - 100
        && dCalendar[YearPeriodNumber] <= MAX (dCalendar[YearPeriodNumber]) - 100
      )
    )

    Subtracting 100 from the 'YearPeriodNumber' column is equivalent to going back a year.

    Below you can see the behaviour of this new Calculated Field:


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Friday, May 22, 2015 6:54 AM Updated DAX formatting
    • Marked as answer by g.hardwick Friday, May 22, 2015 5:44 PM
    Friday, May 22, 2015 6:50 AM

All replies

  • Hi g.hardwick,

    This is most likely caused by the filter context of the grand total in which VALUES ( dCalendar[IRIYearNumber] ) would return multiple year values. For example, when you select 2014 and 2015, the VALUES function in the following part of your DAX formula would return 2014 and 2015 when evaluated within the context of the grand total. This is the scenario that is triggering the error.

    dCalendar[IRIYearNumber]
                = VALUES ( dCalendar[IRIYearNumber] ) - 1


    To workaround this issue, you could wrap your measure in an IF function and check whether VALUES ( dCalendar[IRIYearNumber] ) will return a single value in a given filter context:

    =
    IF(
        HASONEVALUE ( dCalendar[IRIYearNumber] ),
        CALCULATE (
            [Sales $],
            FILTER (
                ALL ( dCalendar ),
                dCalendar[IRIYearNumber]
                    = VALUES ( dCalendar[IRIYearNumber] ) - 1
                    && CONTAINS (
                        VALUES ( dCalendar[PeriodNumber] ),
                        dCalendar[PeriodNumber], dCalendar[PeriodNumber]
                    )
            )
        )
    )

    For the grand total, HASONEVALUE( dCalendar[IRIYearNumber] ) would evaluate to false and cause a blank result to be returned.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, May 21, 2015 8:26 PM
  • Thank you for the help.

    That fixes the error, but it doesn't calculate the combined prior year sales for the selected periods. For example, say I want to look at the last twelve weeks of sales starting in January? The function would return a blank. If I wanted to look at rolling sales for the last 52 weeks starting at anytime in the year I couldn't do that either.

    Are there any tweaks to the calender I can make? The calendar table is here:  https://healthypet.box.com/s/8t993xi413bol6c52b2g41vi2xhmwyu6

     
    • Edited by g.hardwick Thursday, May 21, 2015 10:44 PM
    Thursday, May 21, 2015 10:39 PM
  • Hi g.hardwick,

    You could add another column to your date table that contains IRIYearNumber and PeriodNumber values combined in YYYYMM format. You can do this by creating a Calculated Column with the following DAX and calling it 'YearPeriodNumber'. Be sure to set the data type to 'Whole Number':

    =dCalendar[IRIYearNumber] & RIGHT("00" & dCalendar[PeriodNumber], 2)

    After doing this, you can try the following DAX formula:

    LY Sales 2 :=
    CALCULATE(
      [Sales $],
      FILTER(
        ALL(dCalendar),
        dCalendar[YearPeriodNumber] >= MIN (dCalendar[YearPeriodNumber]) - 100
        && dCalendar[YearPeriodNumber] <= MAX (dCalendar[YearPeriodNumber]) - 100
      )
    )

    Subtracting 100 from the 'YearPeriodNumber' column is equivalent to going back a year.

    Below you can see the behaviour of this new Calculated Field:


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Friday, May 22, 2015 6:54 AM Updated DAX formatting
    • Marked as answer by g.hardwick Friday, May 22, 2015 5:44 PM
    Friday, May 22, 2015 6:50 AM
  • This works like a charm! I can't thank you enough. 
    • Edited by g.hardwick Friday, May 22, 2015 7:11 PM
    Friday, May 22, 2015 2:43 PM
  • Glad it helped :)

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, May 22, 2015 8:18 PM