Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
STEPH0018
Helper II
Helper II

filtered dCalendar


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.

 

STEPH0018_0-1630000919742.png

 

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

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

Captura.JPGdate slicer.gif

 

You will need a Calendar Table for that though





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

...and the measure calculates what date range?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

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:

Model.JPG

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:

date slicer1.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

 

STEPH0018_0-1630005912967.png

But in table no.

 

STEPH0018_1-1630006028036.png

 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors