Forum Discussion
Two Date Columns in a slicer using DAX
Hello everyone,
This is my first time posting here and will be using this forum more. Now on to my question:
I have a requirement where I need to showcase a count between two columns using a timeline slicer. Both columns are Dates/Time format.
I need to basically count an occurence of a certain date or between two dates, between those two columns using OR and display that count in a card visual. Both columns are in the same table. I was able to get the code that works which is this one:
| Date1 | Date2 |
| 2023/08/08 11:55 AM | |
| 2023/09/12 2:13 PM | 2023/09/12 8:00 AM |
| 2023/07/31 3:12 PM | |
| 2017/03/20 4:03 PM | 2023/06/01 8:00 AM |
Hi LME ,
Please try:Measure = VAR _min_date = MIN('Dates'[Date]) VAR _max_date = MAX('Dates'[Date]) VAR _result = CALCULATE( COUNTROWS('Table'), ('Table'[Date1]>=_min_date && 'Table'[Date1]<=_max_date) || ('Table'[Date2]>=_min_date && 'Table'[Date2]<=_max_date) ) + 0 RETURN _resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
- amitchandak
Super User
LME , Create an independent date table and use that in slicer
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date1] >=_max || 'Table'[Date2] >=_max))Or refer, how to deal with two dates
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUHey again, I don't understand this formula that you have presented namely: "Date1[date]" and "Table[value]". Date1 is a column and not a table and I am not sure what you mean by value.
I am just using one table called new_companyprofile which has two date columns called "Created on" and "Updated on". I want a slicer based on the time period between a certain date and a certain date, the card visual will change with a count of how many dates occured between the two columns in that date range using the OR operator. I have visuals in there too which changes based on the slicer.
In my current slicer, I am using the "Update Date" column so it is only counting that column. I want the slicer to count both the update date and created on column. Refer to my example above. I really appreciate this!
Hi LME ,
Please try:Measure = VAR _min_date = MIN('Dates'[Date]) VAR _max_date = MAX('Dates'[Date]) VAR _result = CALCULATE( COUNTROWS('Table'), ('Table'[Date1]>=_min_date && 'Table'[Date1]<=_max_date) || ('Table'[Date2]>=_min_date && 'Table'[Date2]<=_max_date) ) + 0 RETURN _resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
