Issues in Measure with Date slicer

[sebanmathews]

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

[v-yaningy-msft]

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

[sebanmathews]

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

[v-yaningy-msft]

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

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

[_elbpower]

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.

[sebanmathews]

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

[_elbpower]

what is the error?