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 would need help creating DAX and designing data model.
Table1:
start date | end date | Unit | $ value |
1/1/2010 | 1/1/2011 | A | 100 |
1/3/2010 | 1/3/2011 | B | 200 |
1/4/2010 | 1/4/2011 | C | 300 |
1/5/2010 | 1/5/2011 | D | 100 |
so on | so on | ||
until 2020 | until 2022 |
if (Select date >=start date and select date<= end date ,"y","n") ,and then filter out the data set to "Y" .
User selects any date from the calendar in the slicer ,so based on that the table 1 data should be displayed.
Example if user select 1/2/2010 then the data should display like below based on if else condition.
1/1/2010 | 1/1/2011 | A | 100 |
Hi @Anonymous ,
Check the formula below.
Measure =
IF (
ISFILTERED ( 'CALENDAR'[Date] ),
IF (
SELECTEDVALUE ( 'CALENDAR'[Date] ) >= SELECTEDVALUE ( 'Table'[start date] )
&& SELECTEDVALUE ( 'CALENDAR'[Date] ) <= SELECTEDVALUE ( 'Table'[end date] ),
"y",
"n"
),
IF (
TODAY () >= SELECTEDVALUE ( 'Table'[start date] )
&& TODAY () <= SELECTEDVALUE ( 'Table'[end date] ),
"y",
"n"
)
)
However it is not supported for date slicer to display as calendar visual.
You may need to use custom visual instead.
Best Regards,
Jay
@v-jayw-msft Thanks for helping me on this.
I written DAX which abosutely working fine also added Min(Date) to enable calendar option in date slicer.
var _max = MIN('Date'[Date])
var _today = SELECTEDVALUE('Date'[Date],TODAY())
Return
IF (
ISFILTERED ( 'Date'[Date] ),
CALCULATE(value,FILTER('Table1',_max>=[DATE2] && _max<=[DATE1])),
CALCULATE(value,FILTER('Table1',TODAY()>=[DATE2] && TODAY()<=[DATE1]))
)
when I ran the report by clicking clear the filters ,its showing the values default to todays date. But the slicer value is still showing as Min(Date) value, in my case it showing as 1/1/2010.
Is there any way to pass today's date to slicer as default value.
@Anonymous
Create the following measure, replace the correct table names and columns. Add a slicer for dates and make it single select.
Measure1 =
VAR __DateSelected = MAX('Calendar'[Date]) Return
IF(
__DateSelected >= max(Table[StartDate]) && __DateSelected <= max(Table[EndDate]) ,
1,
0
)
Insert a Table Visual on the canvas, add Start Date, End Date, and other fields you need, Go to Filter Pane and add the new measure and set to equal 1.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
There is no relationship between date and table 1. Does this formula works?
@amitchandak thanks amit , the formula is working. But there are a couple of challenges.
1) The defult set to Today's date
2) User needs calendar option in slicer.
I have gone through below Video in You tube. this workaround is working but how to merge these two formuals.
https://www.youtube.com/watch?v=zhWtU0DynCk
My formula as below to set as default value to todays date.
@Anonymous , if there is no relation between date and table. It will work better. All or allselected might not be needed in that case
@Anonymous , Try a measure like.
measure
var _max = maxx(allselected(Date), Date[Date])
return
calculate(sum(Table[Value]), filter(Table, Table[Start Date] <=_max && Table[End Date] >=_max))
or use all of allselected
measure
var _max = maxx(allselected(Date), Date[Date])
return
calculate(sum(Table[Value]), filter(all(Table), Table[Start Date] <=_max && Table[End Date] >=_max))
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |