cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Date Modelling with date and other slicers

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? 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @MikeKK 

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:

v-robertq-msft_0-1607671834867.png

 

  1. 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:

v-robertq-msft_1-1607671834878.png

 

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.

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi, @MikeKK 

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:

v-robertq-msft_0-1607671834867.png

 

  1. 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:

v-robertq-msft_1-1607671834878.png

 

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.

View solution in original post

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!

Responsive Resident
Responsive Resident

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.

Responsive Resident
Responsive Resident

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

Helper II
Helper II

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? 

Responsive Resident
Responsive Resident

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. 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors