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.
Hi,
Is it possible to create a column which has dynamic data based upon the slicer? For example,
A project has a start date and an end date. I will have a slicer in whcih I will specify a range of dates. Then, my column will give me the number of days that project has in the selected range of dates.
Solved! Go to Solution.
Hi @SaurabhGupta,
Try this formula please.
Measure = VAR minDate = MIN ( 'calendar'[date] ) VAR maxDate = MAX ( 'calendar'[date] ) VAR startDate = MIN ( project[start date] ) VAR endDate = MIN ( project[end date] ) RETURN SWITCH ( TRUE (), startDate < minDate && endDate < minDate, 0, startDate > maxDate && endDate > maxDate, 0, startDate < minDate && endDate > maxDate, DATEDIFF ( minDate, maxDate, DAY ), startDate < minDate && endDate > minDate && endDate < maxDate, DATEDIFF ( minDate, endDate, DAY ), endDate > minDate && endDate < maxDate && startDate > minDate && startDate < maxDate, DATEDIFF ( startDate, endDate, DAY ), endDate > maxDate && startDate > minDate && startDate < maxDate, DATEDIFF ( startDate, maxDate, DAY ) )
Best Regards,
Dale
Yes,if your data model has the supporting data, this is possble.
Thanks
Raj
Thanks Raj,
Could you please tell me how to do that?
I have Project start date, project end date, and a calendar table (that will be used in a slicer for selecting the range of dates).
How can I create a new column in my project table which will show the number of days that a project has in the selected range of dates?
Hi @SaurabhGupta,
Try this formula please.
Measure = VAR minDate = MIN ( 'calendar'[date] ) VAR maxDate = MAX ( 'calendar'[date] ) VAR startDate = MIN ( project[start date] ) VAR endDate = MIN ( project[end date] ) RETURN SWITCH ( TRUE (), startDate < minDate && endDate < minDate, 0, startDate > maxDate && endDate > maxDate, 0, startDate < minDate && endDate > maxDate, DATEDIFF ( minDate, maxDate, DAY ), startDate < minDate && endDate > minDate && endDate < maxDate, DATEDIFF ( minDate, endDate, DAY ), endDate > minDate && endDate < maxDate && startDate > minDate && startDate < maxDate, DATEDIFF ( startDate, endDate, DAY ), endDate > maxDate && startDate > minDate && startDate < maxDate, DATEDIFF ( startDate, maxDate, DAY ) )
Best Regards,
Dale
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |