Forum Discussion
Challenge with date calcs
Hi - seeking help from this great community!
I am trying to prepare a table that has full quarter (QTR) and QTD information at the same time using sample data below:
Data:
Product Date Amt
A 01/01/2020 100
A 15/02/2020 200
A 17/03/2020 150
I have active slicers for year and month components to enable the QTD calculation. So if I were to pick Year = 2020 and month = Feb, here's what I would want to see as output in a table:
QTD QTR
Product A 300 450
The QTD calc works fine....but I can't seem to figure out how to get a measure that essentially follows the year slicer while at the same time ignores the specific month slicer for filtering yet uses that month to identify the relevant quarter I need.
Thanks for your insights!
Something like this (there are many ways)
QTR=
var currQ = max(Dates[Quarter])
Return Calculate([amt],Dates[Quarter] = currQ)
3 Replies
- lbendlin
Super User
use CALCULATE to break out of the Month filter context but keep the Quarter filter context.
Could you please share an example of what the DAX formula syntax would look like? Note that I currently do not have a filter for quarter - I only have slicers for Year and Month.
My current measures are:
QTD = calculate ([amt], datesqtd (dates))
QTR = ????
I'm hoping to avoid the need of having a slicer for quarter, as that would seem repetitive once the month is chosen.
- lbendlin
Something like this (there are many ways)
QTR=
var currQ = max(Dates[Quarter])
Return Calculate([amt],Dates[Quarter] = currQ)
