Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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:
Slicer1 =
CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
Slicer2: Click “Enter data” to create this table:
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.
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:
Slicer1 =
CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
Slicer2: Click “Enter data” to create this table:
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.
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!
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.
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
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |