cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mujahid Frequent Visitor
Frequent Visitor

Dynamics Date Calendar by Visual slicers

I have 3 tables one has Pre Days means 1,2,3,4 and 2nd has post dates. I have a calender table which has some hard coded dates but I want to change start and end dates by appending the Predates in start and EndDates in end.

 

Table1 = CALENDAR(DATEVALUE(DATE(2013,07,24))+SELECTEDVALUE(LowerThreshold[Value]) ,DATE(2013,07,25))
But I am unable to capture selected value in Calender table. 
As I know selectedvalue works in measure but. When I recall that measure in computed column I did not give any impact. While it's adding values in measure. 
Sortly is there any way to perform this task to change start and end date dynamically by visuals.
Capture.JPG
7 REPLIES 7
Super User
Super User

Re: Dynamics Date Calendar by Visual slicers

@Mujahid you cannot pass slicer value when creating tables by dax.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




natelpeterson New Contributor
New Contributor

Re: Dynamics Date Calendar by Visual slicers

@Mujahid -

Calculated Tables and Calculated Columns are not affected by slicers. 

You can filter the Date Table by selections in your slicers. 

One way to implement this:

1. Create a Measure that will be BLANK for rows that should not appear, and "" for rows that should appear.

2. Add that measure to a table visual, along with your date.

3. If you want to hide the measure column, change the header name a single character and decrease the width of the column, so it will not appear. 

 

The measure could be something like this:

Include Date Range = 
var minDate = <Your Logic>
var maxDate = <Your Logic>
var dateToAssess = MAX('Date'[Date])
return 
IF(
    dateToAssess >= minDate && dateToAssess <= maxDate,
    "",
    BLANK()
)
Mujahid Frequent Visitor
Frequent Visitor

Re: Dynamics Date Calendar by Visual slicers

I have multiple Start and End dates not a single. Initially I planned to create a table with calender and Union all dates. If If I use this measure so is it possible to append more filters with more min and max dates.

natelpeterson New Contributor
New Contributor

Re: Dynamics Date Calendar by Visual slicers

@Mujahid  - I'm not sure exactly what you're trying to do, but here are some tables and measures that will hopefully be a good demo of the possibilities:

 

Create 2 Date Tables:

 

Date1 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
Date2 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))

Create a Number table for adding to the dates:

Numbers = GENERATESERIES(0,100,1)

Then a Measure to limit the dates:

Include Date Range = 
var minDate = MIN('Date2'[Date]) + max(Numbers[Number])
var maxDate = MAX('Date2'[Date]) + max(Numbers[Number])
var dateToAssess = MAX('Date'[Date])
return
IF(
    dateToAssess >= minDate && dateToAssess <= maxDate,
    "",
    BLANK()
) 

Here is the result (you need to Edit Interactions, so that one Number slicer will affect one table visual:

 

Multiple Date Ranges With Offset.PNG

Mujahid Frequent Visitor
Frequent Visitor

Re: Dynamics Date Calendar by Visual slicers

Can you  please Share Pibx

Mujahid Frequent Visitor
Frequent Visitor

Re: Dynamics Date Calendar by Visual slicers

I ran that file. It's truncating days dynamically as I select something. 

My Case is bit different. 

For Instance I have a table for Sales data. which has field name [Sales_Date] of multiple years. We have easter date slots (for 30 days each slot) for each year means start date of season  and end date of season. So I created a table as you create with calender function and used union to uniform all years data means  union(calender (start date, enddate),calender (start date, enddate),.....).

Then I created a relationship between date table and sales table. All things working fine at this stage.

Now Next thing is to add a visual if user want to see sales data preceeding N Days and Post N Days with eastern. Like it's 04/01/2019 to 04/30/2019 so user can see data for 03/26/2019 to 04/30/2019 so N Days preceeding 4. because User want to see N Days before easter with easter season. 

Where I used technique mentioned about but It did not work by slicer.

If there is an other work around do let me know If i am using invalid strategy to cope up this issue. 

Thank you 

Mujahid Frequent Visitor
Frequent Visitor

Re: Dynamics Date Calendar by Visual slicers

We need two slicers. One for Preceeding Easter and One for Post Easter.