none
DAX query not returning prior quarter value when filtering specific quarters

    Question

  • Hello,

    I am observing cases were the time intelliegence functions are not returning values for specific dates. This was observed with the AdventureWorksTabular Model SQL 2012 instance with the measure Internet Previous Quarter Gross Profit which is defined as follows in Internet Sales:

    CALCULATE([Internet Total Gross Profit],PREVIOUSQUARTER('Date'[Date]))

    The following query below is to obtain the current quarter's profit and the prior quarter's profit for each quarter in 2006:

    evaluate summarize(
    Filter('Internet Sales',RELATED('Date'[Calendar Year])="2006"),
    'Date'[Calendar Year], 'Date'[Calendar Quarter], 
    "PROFIT", [Internet Total Gross Profit], 
    "PREVIOUS QUARTER PROFIT",  [Internet Previous Quarter Gross Profit])

    This query returns the following:

    Date[Calendar Year]  Date[Calendar Quarter] [PROFIT] [PREVIOUS QUARTER PROFIT]
    2006	1	719198.9043	728276.31
    2006	2	812350.7911	719198.9043
    2006	3	565555.6995	812350.7911
    2006	4	549744.7569	565555.6995

    However if the query is change to filter on a specific quarter such as the 3rd quarter of 2006 no data is return for the prior quarter profit. It was expected that 812350.7911 would be returned however a null is returned. Below is the query and the result:

    evaluate summarize(
    Filter('Internet Sales',AND(RELATED('Date'[Calendar Year])="2006",RELATED('Date'[Calendar Quarter])="3")),
    'Date'[Calendar Year], 'Date'[Calendar Quarter], 
    "PROFIT", [Internet Total Gross Profit], 
    "PREVIOUS QUARTER PROFIT",  [Internet Previous Quarter Gross Profit])
    
    Date[Calendar Year] Date[Calendar Quarter] [PROFIT [PREVIOUS QUARTER PROFIT]
    2006	3	565555.6995	

    The query above will return the prior quarter profit for quarters 1, 2, and 4. It appears not to work for quarter 3 specifically.  Is this an known issue or expected behavior ?

    Thanks.





    • Edited by rthar Thursday, May 23, 2013 6:54 PM
    Thursday, May 23, 2013 6:50 PM

Answers

All replies