Re: PREVIOUSQUARTER returns nothing

misterian · ‎01-08-2018

Hello, this really has me stumped as everything else works OK! I can count and average if I put in manually the dates and the current quarter time intellignce works but not the previous quarter ones.

These are measures referenced below

Alarms Total = COUNTROWS(system)
Response Avg = AVERAGE(system[ResponseTime])

Count for Current Quarter - works

Alarms QTD = TOTALQTD([Alarms Total],'Date'[Date])

Count for Previous Quarter - works - yes the dates are manual for testing this

Alarms Prev Qx = 
CALCULATE(
    [Alarms Total],
    FILTER (
       ALL(system),
        AND( 
           system[AlarmTime].[Date] >= date("2017","10","1"),
            system[AlarmTime].[Date] <= date("2017","12","31")
        )
    )
)

Count for Previous Quarter - doesn't work (blank)

Alarms Prev Q =
CALCULATE (
    [Alarms Total],
     PREVIOUSQUARTER('Date'[Date])
)

Average this Quarter to date - works

Response QTD Avg = TOTALQTD(AVERAGE(system[ResponseTime]),'Date'[Date])

Average for Previous Quarter - works - again manual dates for testing

Response Prev Qx Avg = 
CALCULATE(
    [Response Avg],
    FILTER (
       ALL(system),
        AND( 
           system[AlarmTime].[Date] >= date("2017","10","1"),
            system[AlarmTime].[Date] <= date("2017","12","31")
        )
    )
)

Finally, Average for Previous Quarter - doesn't work (blank)

Response Prev Q Avg =
CALCULATE ( 
    AVERAGE ( system[ResponseTime] ),
     PREVIOUSQUARTER( 'Date'[Date] )
)

mattbrice · ‎01-08-2018

i thing i see is you are using two different calendar tables. 'Date'[Date] with time intelligence, and system[Alarmtime].[Date] when computing manually. Which is used in visual? And does 'Date'[Date] have all contiguous dates needed?

misterian · ‎01-08-2018

Thanks, I suggest you open the PBIX file in the previous reply, as it will be faster for you and more informative that me trying to write a long post.

For time intelligence the date table is used. The manual ones need the time from the same table as the average or calcrows value comes from. 'Date'[Date] uses the DAX create table from max and min dates for it. The bar graph works so this should all be OK.

Interkoubess · ‎01-08-2018

Hi @misterian

I opened your file and make a quick test, if you create a filled map with calendar date and let's vary the date you will notice that the Response Prev Q Avg works...

The system needs to know what is the previous period so if you choose then the system can generate the previous quarter...

Let us know if it is not working..

mattbrice · ‎01-08-2018

I also downloaded the file, and to expand on the previous comment, PREVIOUSQUARTER finds the earliest single date visible in the current filter context, then returns the quarter previous to it. In this case since you don't select a date the earliest visible is the first date in the 'Date' table and therefore can't go back any more. So PREVIOUSQUARTER returns blank, which causes the CALCULATE to return blank.

misterian · ‎01-08-2018

Thanks, that makes sense. So what good is previous quarter? Can I feed a context from as the current date to the previous quarter or just find a different way to do this without previousquarter. Quarters are a little harder than years and months because quarters may "overlap" years.

mattbrice · ‎01-08-2018

I usually prefer to put slicers on the page for Year/Quarter/Month so people can interact with report, but you can do this:

Response Prev Q Avg = 
var _today = FILTER( VALUES( 'Date'[Date] ), 'Date'[Date] = TODAY() )
RETURN
CALCULATE ( AVERAGE(system[ResponseTime]), PREVIOUSQUARTER( _today ) )

misterian · ‎01-08-2018

I'm not sure I understand what you are getting at. I need to have a card showing only the previous month's value, nothing else as part of my dashboard. The user must see only the previous Q relative to now without any interaction with the report.