topic Re: Challenge with date calcs in DAX Commands and Tips

Challenge with date calcs

madscientist — Sun, 07 Jun 2020 16:08:40 GMT

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!

Re: Challenge with date calcs

lbendlin — Sun, 07 Jun 2020 17:22:13 GMT

use CALCULATE to break out of the Month filter context but keep the Quarter filter context.

Re: Challenge with date calcs

madscientist — Sun, 07 Jun 2020 17:38:00 GMT

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.

Re: Challenge with date calcs

lbendlin — Sun, 07 Jun 2020 18:03:17 GMT

Something like this (there are many ways)

QTR=

var currQ = max(Dates[Quarter])

Return Calculate([amt],Dates[Quarter] = currQ)