topic Re: filtered dCalendar in DAX Commands and Tips

filtered dCalendar

STEPH0018 — Thu, 26 Aug 2021 18:03:00 GMT

Hi, guys. It is possible to create a filtered table, so that it is an auxiliary dCalendar, with a date range according to the date chosen by the user? For example, in the graph below, I would like only the data from 12/31/2021 to 01/09/2021 to appear. That is, instead of putting my complete dCalendar on the x-axis I would put the filtered.

Re: filtered dCalendar

PaulDBrown — Thu, 26 Aug 2021 19:08:06 GMT

Can't you use the regular date slicer set to "Between"?

You will need a Calendar Table for that though

Re: filtered dCalendar

STEPH0018 — Thu, 26 Aug 2021 19:11:27 GMT

Hi, Paul, the intention is for the user to choose only one date.

Re: filtered dCalendar

PaulDBrown — Thu, 26 Aug 2021 19:14:03 GMT

...and the measure calculates what date range?

Re: filtered dCalendar

STEPH0018 — Thu, 26 Aug 2021 19:17:28 GMT

SELECTEDVALUE(dCalendario[DATA]) and

LASTDATE(PARALLELPERIOD(dCalendario[DATA],-1, YEAR))

Re: filtered dCalendar

PaulDBrown — Thu, 26 Aug 2021 19:58:26 GMT

Do you want to see the data for the last year based on the date selected?

See if this works for you.

1) Duplicate you date table using (I've called the duplicate "Period Filter") new table in the ribbon and

Period Filter = 'Calendar Table'

Join this table to the Calendar table in a1-to many inactive relationship with a single filter direction from the Period Filter Table to the Calendar Table. The model should look like this:

Now add the slicer from the calendar table, and create the visual using the date field from the Period filter table as the axis.

Create a measure along these lines:

Data by sel date = VAR CalD = MAX('Calendar Table'[Date]) VAR SelPeriods = DATESINPERIOD('Period Filter'[Date], CalD, -1, YEAR) RETURN CALCULATE([Sum of Sales], REMOVEFILTERS('Calendar Table'), KEEPFILTERS(SelPeriods), USERELATIONSHIP('Calendar Table'[Date], 'Period Filter'[Date]))

Add this measure to the visual and you will get:

Re: filtered dCalendar

STEPH0018 — Thu, 26 Aug 2021 19:27:17 GMT

when I put it on a card it appears as the dates I want.

But in table no.

Re: filtered dCalendar

PaulDBrown — Fri, 27 Aug 2021 21:00:47 GMT

Yes. My sincere apologies for my previous comment (which I have deleted since I realised how silly and, of course, wrong it was).
have you tried the solution I posted?

Re: filtered dCalendar

STEPH0018 — Thu, 26 Aug 2021 20:32:44 GMT

yes, but it seems it doesn't understand that the 9th is the selected data.

Re: filtered dCalendar

STEPH0018 — Fri, 27 Aug 2021 19:57:57 GMT

Hi, Paul, in my case it didn't work like yours, the x axis didn't change.

Re: filtered dCalendar

PaulDBrown — Mon, 30 Aug 2021 10:13:49 GMT

do you have "Auto Date-Time " selected in the file options?