topic Re: Slicer 2 months ago in DAX Commands and Tips

Slicer 2 months ago

Anonymous — Mon, 06 Apr 2020 10:41:49 GMT

Hi,

Im trying to get my report to show, upon selecting year-month in the slicer, 1 month and 20 days ago.

So If I select 2020-04 I need my table visual to only display values with a required fix date in the range 1 Feb - 10 March.

This needs needs to be moving with the slicer months. My code currently is below but its not aligning with the dates.

8. =

var _startDate = DATEADD(DimDate[Date],-2,MONTH)

var _startPeriod = STARTOFMONTH(_startDate)

var _endDate = DATEADD(DimDate[Date], -1, MONTH)

var _endPeriod = STARTOFMONTH(_endDate) + 9

RETURN

CALCULATE(SUMX(vwtest,vwtest[Count1]), DATESBETWEEN(vwtest[RequiredFix], _startPeriod, _endPeriod) )

Am I approaching this correctly, or do I have to be amending the formula for the measure?

Thanks in advance

Liam

Re: Slicer 2 months ago

az38 — Mon, 06 Apr 2020 11:15:14 GMT

Hi Anonymous

DATEADD() is a very tricky function

according https://docs.microsoft.com/en-us/dax/dateadd-function-dax

The result table includes only dates that exist in the dates column. If the dates in the current context do not form a contiguous interval, the function returns an error.

I recommend you to try first to replace dateadd() to statements like

var _endDate = DATE(YEAR(DimDate[Date]), MONTH(DimDate[Date])-1, DAY(DimDate[Date]) )

Re: Slicer 2 months ago

Greg_Deckler — Mon, 06 Apr 2020 11:29:22 GMT

Well, you are if you are getting the correct result! 🙂

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you are not getting the intended result, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

Re: Slicer 2 months ago

Anonymous — Mon, 06 Apr 2020 12:14:20 GMT

Hi az38 ,

Many thanks for your replies with this.

It does feel like its not possible though to do with a table visual and a rolling month slicer 2 months in advance.

Thanks

Liam