topic Re: Issues in Measure with Date slicer in DAX Commands and Tips

Issues in Measure with Date slicer

sebanmathews — Tue, 23 Jan 2024 11:06:56 GMT

Hi,
We have a table for storing event details (EventTable) with Startdate and Enddate.In Power BI report we have a date range slicer. Based on the date slicer, we need to show the number of events in the date slicer duration as below:

0-6 months
6-9 months
9-12 months
In progress

Here, "In progress" indicates the event was not finished during the slicer date range even though the event finished after the date slicer end date.
Challenge we are facing is in the measure, In this measure we are not getting the if condition evaluate properly.

EventDurationMeasure =
var _rangeStart=CALCULATE(MIN(DateRange[Date]),ALLSELECTED('DateRange'))
var _rangeEnd=CALCULATE(MAX(DateRange[Date]),ALLSELECTED('DateRange'))
return
IF(SELECTEDVALUE(EventTable[EndDate]) > _rangeEnd, "In progress", "0-6 mnd") // To add other date ranges

Here, always it shows the else condition (0-6 mnd) and the total count without considering the date slicer range

Kindly guide me whats wrong here 🙂

Thank you all for the support

Re: Issues in Measure with Date slicer

_elbpower — Tue, 23 Jan 2024 11:34:02 GMT

EventDurationMeasure = VAR _rangeStart = CALCULATE(MIN(DateRange[Date])) VAR _rangeEnd = CALCULATE(MAX(DateRange[Date])) VAR _eventStartDate = SELECTEDVALUE(EventTable[StartDate]) VAR _eventEndDate = SELECTEDVALUE(EventTable[EndDate]) VAR _eventStatus = IF( _eventEndDate > _rangeEnd, "In progress", IF( _eventEndDate >= _rangeStart && _eventEndDate <= DATEADD(_rangeStart, 6, MONTH), "0-6 mnd", IF( _eventEndDate > DATEADD(_rangeStart, 6, MONTH) && _eventEndDate <= DATEADD(_rangeStart, 9, MONTH), "6-9 mnd", IF( _eventEndDate > DATEADD(_rangeStart, 9, MONTH) && _eventEndDate <= DATEADD(_rangeStart, 12, MONTH), "9-12 mnd", BLANK() ) ) ) ) RETURN _eventStatus

This measure uses DATESBETWEEN to calculate the events falling within the date range defined by the slicer. It then checks the event end date against different date ranges and assigns the appropriate label ("0-6 mnd", "6-9 mnd", "9-12 mnd", "In progress").

Make sure to replace the column names and adjust the logic as needed based on your actual data model.

Re: Issues in Measure with Date slicer

Anonymous — Wed, 24 Jan 2024 09:23:43 GMT

Hi, sebanmathews

According to your description, I can not see any error in the IF condition you wrote, it displays "In process" and "0-6 mnd" correctly. You can try the _elbpower method, if it doesn't work, you need to share the pbix file without sensitive data, the datasheet, the desired output and the logic to achieve the output.

Best Regards,
Yang
Community Support Team

If 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

Re: Issues in Measure with Date slicer

sebanmathews — Wed, 24 Jan 2024 15:06:27 GMT

Thank you for your response.

When I display the individual rows in table it looks fine. But when I added count of EventTable rows instead of 'EndDate' , then I am not getting the proper result

Re: Issues in Measure with Date slicer

sebanmathews — Wed, 24 Jan 2024 15:09:48 GMT

Thank you for the help. By using this measure, I am not getting the result.It is giving errors in the measure.

Re: Issues in Measure with Date slicer

Anonymous — Wed, 31 Jan 2024 07:09:46 GMT

Hi, sebanmathews

You can share the pbix file without sensitive data or post a complete reproduction of the problem with the data table, measure, desired effect, and the logic to achieve the effect and other information.

Best Regards,
Yang
Community Support Team

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Issues in Measure with Date slicer

_elbpower — Fri, 02 Feb 2024 08:00:28 GMT

what is the error?