locked
Dax Calculating Same time previous period with nonstandard periods RRS feed

  • Question

  • I need help dealing with prior period calculations where the periods are not standard.

    In our business we define these time periods based on 8 to 10 week periods that really have no correlation to standard weeks, months or quarters.  Additionally there may be some time between these periods that are not defined. For example:

    Period 1 (p1) = June 18 - Sept 2

    Period 2 (p2) = Sept 17 - Dec 9

    The length of the periods are variable and when one ends another does not necessarily begin.

    I need to figure out how to compare the sum of a metric in Period 2 with the sum of a metric in Period 1.

    I have a standard Date dimension. And I have a period deminsion with the period labels (p1, p2 etc.) the start date, the end date of the periods, and a column called previousPeriod that has the label of the prior period.

    Each Fact has a Period ID so that I can use period as a dimension.One of my measures is Sum(sales).

    I want it so that if I am viewing the Tabular cube by period, I can have the sum of sales for that period, and the prior one. I hope it is clear what I am trying to do.

    Friday, March 21, 2014 2:39 PM

Answers

  • You should add to your Date table a column that contains an incremental number for each period, then write something like:

            CALCULATE (
                [Sales],
                ALL ( 'Date' ),
                FILTER (
                    ALL ( 'Date'[IncrementalPeriod] ),
                    'Date'[IncrementalPeriod]
                        = EARLIER ( 'Date'[IncrementalPeriod] ) - 1
                )
            )

    I suggest you this pattern that will be published on March 31 - it contains many more examples, including how to write the make this formula working also with a partial arbitrary selection of days in the selected period:

    http://www.daxpatterns.com/time-patterns


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Proposed as answer by Michael Amadi Sunday, March 23, 2014 7:16 PM
    • Marked as answer by Flampt Monday, March 24, 2014 12:25 PM
    Saturday, March 22, 2014 6:01 PM

All replies

  • You should add to your Date table a column that contains an incremental number for each period, then write something like:

            CALCULATE (
                [Sales],
                ALL ( 'Date' ),
                FILTER (
                    ALL ( 'Date'[IncrementalPeriod] ),
                    'Date'[IncrementalPeriod]
                        = EARLIER ( 'Date'[IncrementalPeriod] ) - 1
                )
            )

    I suggest you this pattern that will be published on March 31 - it contains many more examples, including how to write the make this formula working also with a partial arbitrary selection of days in the selected period:

    http://www.daxpatterns.com/time-patterns


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    • Proposed as answer by Michael Amadi Sunday, March 23, 2014 7:16 PM
    • Marked as answer by Flampt Monday, March 24, 2014 12:25 PM
    Saturday, March 22, 2014 6:01 PM
  • I went ahead and tried it. But the measure gives me the following error:

    ERROR - CALCULATION ABORTED: Calculation error in measure 'Sales'[PreviousPeriodSales]: EARLIER/EARLIEST refers to an earlier or context which doesn't exist.

    What I did to try and make the calculation simple is I went ahead and made the periods continuous.

    So even though the periods looked like this:

    Period 1 (p1) = June 18 - Sept 2

    Period 2 (p2) = Sept 17 - Dec 9

    I gave all the dates June 18 - Sept 15 one id, and Sept 17 - Dec 31 another id. All dates in my date dim have an id.

    Monday, March 24, 2014 1:06 PM
  • If you use it in a measure, you should probably write:

           CALCULATE (
                [Sales],
                ALL ( 'Date' ),
                FILTER (
                    ALL ( 'Date'[IncrementalPeriod] ),
                    'Date'[IncrementalPeriod]
                        MAX ( 'Date'[IncrementalPeriod] ) - 1
                )
            )

    Please note this works only if you select one period - if there are more periods selected, it returns the one prior to the last selected (you might use MIN instead of MAX to return the one prior to the first selected).


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Monday, March 24, 2014 8:18 PM
  • Marco,

    This would be great content for a Wiki article:

    http://social.technet.microsoft.com/wiki/contents/articles/23330.technet-guru-contributions-for-march.aspx#SQL_BI_amp_Power_BI

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, March 27, 2014 5:39 AM
  • Ed, the article will be published on www.daxpatterns.com next Monday - it's not a good practice to duplicate content in different web sites, also for search engine rankings.


    Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo

    Thursday, March 27, 2014 5:42 AM