topic Re: Show 30 days data in visual before the date selected in date slicer in DAX Commands and Tips

Show 30 days data in visual before the date selected in date slicer

Anonymous — Tue, 01 Dec 2020 18:24:29 GMT

Hi All,

I have a date slicer and a line chart. My requirement is on selecting a date in date slicer , 30 days of data should be displayed in line chart ahead of date selected in date slicer.

If someone came across same scenario. Please suggest the solution.

Regards

Vaishali

Re: Show 30 days data in visual before the date selected in date slicer

CNENFRNL — Tue, 01 Dec 2020 20:57:49 GMT

Hi, Anonymous , the solution is a bit tricky given the fact that filter (selection of a date in the slicer) propogates from slicer to chart,

New Measure = VAR __date_selected = MAX ( 'Calendar'[Date] ) // assuming there exists a calendar table in your model, and Calendar[Date] is sliced VAR __period = DATESINPERIOD ( 'Calendar'[Date], __date_selected, -30, DAY ) VAR __inbetween = CALCULATE ( MAX ( factTable[Date] ), ALL ( 'Calendar'[Date] ) ) IN __period // slcier must be removed in order to keep all other dates in sight RETURN IF ( __inbetween, [Old Measure] )

Re: Show 30 days data in visual before the date selected in date slicer

amitchandak — Wed, 02 Dec 2020 02:40:06 GMT

Anonymous , You can get the last 30 days like the example with help from the date table

Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date],-12,DAY)))

But this will not give trend for last 30 Days. Follow the approach in the video

https://www.youtube.com/watch?v=duMSovyosXE

Re: Show 30 days data in visual before the date selected in date slicer

Anonymous — Wed, 02 Dec 2020 04:51:29 GMT

amitchandak Thanks for the logic

The logic is working fine but still the chart is showing data only for the date selected in the slicer and not of past 12 or 30 days.

Re: Show 30 days data in visual before the date selected in date slicer

Anonymous — Wed, 02 Dec 2020 05:20:27 GMT

CNENFRNL

I tried you logic but it is giving me only two dates. It will be more helpful , if you could share a sample PBI file for the same.

Re: Show 30 days data in visual before the date selected in date slicer

CNENFRNL — Wed, 02 Dec 2020 10:56:38 GMT

Anonymous , as you wish, I attached a pbix file for your reference.

It's a simplified project I was once engaged in. It's with more flexibilities to display previous 1 ~ 30 days before a chosen date.