Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sebanmathews
New Member

Issues in Measure with Date slicer

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

6 REPLIES 6
v-yaningy-msft
Community Support
Community Support

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.

 

vyaningymsft_0-1706088190339.png

 

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

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

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
Resolver III
Resolver III

 

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.

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

what is the error? 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors