Date Modelling with date and other slicers

[Anonymous]

I am buidling a report that allows user to choose see the data and visuals during the selected data period.  The data period is based on two slicers value:

Slicer 1 - Start Date.   Slicer 1 option:  month value, from past 6 months to next 6 months

Slicer 2 - Time Frame. Slicer 2 option: 1-2 months, 3-4 months, 7-12 months.

The data period start date depends on slicer 1 value, while end date is calculated by slicer 1 and slicer 2.

e.g. If slicer 1 = Jan20,  slicer 2 = 1-2 months.  Start date will be 1-Jan-20, end date will be 29-Feb-20.

e.g. If slicer 1 = May20,  slicer 2 = 3-4 months.  Start date will be 1-Jul-20, end date will be 31-Aug-20.

Should I and how do I build relationship of start date and end date with Date table? 

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and examples, I can understand your logic, but I don’t think it can be achieved by building a relationship of start date and end date with the Date table. I suggest you to achieve this by using measures, you can follow my steps:

1. Create two tables ‘Slicer1’, “Slicer2”:

Slicer1 =

CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

Slicer2: Click “Enter data” to create this table:

2. Create two measures:

Start date =

var _selecteddate=MIN('Slicer1'[Date])

var _selectedperiod=SELECTEDVALUE(Slicer2[option])

return

SWITCH(_selectedperiod,

"1-2 months",_selecteddate,

"3-4 months",EDATE(_selecteddate,2),

"7-12 months",EDATE(_selecteddate,6))

End date =

var _selecteddate=MIN('Slicer1'[Date])

var _selectedperiod=SELECTEDVALUE(Slicer2[option])

return

SWITCH(_selectedperiod,

"1-2 months",EDATE(_selecteddate,2)-1,

"3-4 months",EDATE(_selecteddate,4)-1,

"7-12 months",EDATE(_selecteddate,12)-1)

Then create two Slicers and two card charts to place two measures, you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Yes, that is what I thought. It is not possible to build relationship to Date table if we are looking at Start and End Date through two non-date slicers.      Thank you!

[Anonymous]

Creating 1 slicer to check 2 dates isnt possible unless you create measures measures and more measures.

You could copy your date table and create a relation between startdate and datetable1 and a relation between enddate and datetable2

I doubt the requirement however. I would expect a requirement like 'i want to see all blabla which have started in period a to b and have been finished in 2 months (or 4 or whatever)' 

Not i want to check begin and enddate.

[Anonymous]

I dont think your description says that. If i read the description the the startdate determines which bucket you are in

Start 20 jan is bucket 1 (1-2) months

Start 20 may is bucket 2

So the bucket had nothing to do with the enddate (if i read the description)

IF you mean the bucket is dependant on the start and enddate (eg the duration of the occurance) then i would say

calculate the occurance in the fact (datediff(days,startdate,enddate)

If < 60 bucket 1

if between 60 and 120 bucket 2 etc

This goes in the fact and you create a slicer on it

[Anonymous]

End date is required so that the visual only shows data/value between start date to end date. 

The start and end date are dynamic (based on slicer value),  how do I create the buckets in table? 

[Anonymous]

I dont get why you want to link to enddate. If you know the begindate you know which time period (bucket) you are in.

So several options here

1) Create the buckets in the fact table. Then create a Dimension with category number / description.

2) Create the buckets in the date table.