Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create a dynamic date table from a given start date based on the selection of a filter

I have researched this idea, and I've not found anything that covers it entirely. 

 

Basically, I have two tables, say an Employee (dim), and a Date table (dim) that relate to two fact tables where I'm taking my measures. The Employee table has employees in it with a specific start date per employee; this is the date where I want to use as a start date to generate a table (if a new date table is needed) to feed into a line chart that goes unto today. Based on the filter selection for a single employee, it should take the associated start date in the employee table, feed that start date as the start of the x-axis on a line chart, which goes until TODAY() for only the employee selected in the filter. 

 

Currently if selecting an employee, they show up on the line chart as a single dot (since they only have their state date as a reference point. But it should be a range of dates on the x-axis (for some measure on the y-axis), starting with their unique start date in the employee table with their name. 

 

Any help on this would be appreciated. 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Anonymous ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _select=SELECTEDVALUE('Table'[Employee])
var _mindate=MINX(FILTER(ALLSELECTED('Table'),'Table'[Employee]=_select),'Table'[Date])
return
IF(
    MAX('Date'[Date]) >=_mindate&&MAX('Date'[Date])<=TODAY(),1,0)
    Measure =
COUNTX(
    ALLSELECTED('Date'),'Date'[Date])

    2. Place [Flag]in Filters, set is=1, apply filter.

    3. Visual – X-axis – Type -- Categorical

    4. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies