Forum Discussion
PREVIOUSQUARTER returns nothing
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] )
)
9 Replies
- Interkoubess
Solution Sage
- misterianRegular Visitor
Excel file is here
- mattbrice
Solution Sage
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?
- misterianRegular Visitor
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
Solution Sage
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..