topic Re: Selection Date Slicer in DAX Commands and Tips

Selection Date Slicer

m_wex — Wed, 14 Jul 2021 02:42:19 GMT

Hi Everyone,

Created a date slicer based on a date table linked to my invoiced sales table. The invoiced sales table only contains data through March 2021 but the slicer shows all quarters for 2021. Running into an issue with previous years' data. If Q1 is selected no issue, YTD and PYTD information is correct. But if 2021 is selected (which includes all 4 quarters) the previous years' sales data shows the whole pervious year versus only the sales through March 2021. Is there a workaround for the slicer so that it only allows selection though the last sales date in the involved sales table?

Thanks M_Wex

Re: Selection Date Slicer

amitchandak — Wed, 14 Jul 2021 05:28:33 GMT

m_wex , As slicer might be coming from Date table having only one-directional join. Then Date slicer will not filter.

You can add measure in visual level filter for slicer and check for not blank value

You can force QTD

QTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESQTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESQTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALQTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LQTD QTY forced=
var _max = date(year(today()),month(today())-3,day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,Quarter)),'Date'[Date]<=_max)
//TOTALQTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,Quarter),'Date'[Date]<=_max)

LQTD QTY forced=
var _max1 = maxx('order',[Order date]) // or //maxx(allselected('Date'), 'Date'[Date]) // or // Today()
var _max = date(year(_max1),month(_max1)-3,day(_max1))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,Quarter)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,Quarter)),'Date'[Date]<=_max)
//TOTALQTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,Quarter),'Date'[Date]<=_max)

Re: Selection Date Slicer

m_wex — Wed, 14 Jul 2021 20:01:41 GMT

Hi Amitchandak,

Thank you. Should I amend the relationship? From single cross filter direction to both?

With the Dax solutions. Is it possible to only show the last invoiced sales dare from the sales table for the date slicer? What I need ultiamtely is to map the last invoice date in the sales fact table to the slicer. That end date could be any date depending on how the fact table is updated. Right now, it is updated on a monthly basis but I am trying to get the report as close to real time as possible.

M_Wex