CODEWITHSUNDEEP
powerbidaxbetween
unnikrishnan
unnikrishnan

Reputation: 151

How to show the records which are part between selected date range in Dax

I have a below table in Power BI. This table will have the details of campaigns which are ran on specific period.

Campaign Name   StartDate   Enddate
AAA             01-05-2022  30-04-2022
BBB             01-04-2022  30-04-2022
CCC             01-04-2022  30-04-2022
DDD             01-04-2022  30-09-2022
EEE             01-03-2022  30-09-2022
FFF             01-03-2022  30-09-2022

Now I am using the start date in the slicer. so if I select date range of Apr-22 to Jun-22, table should display whatever campaigns which are active between the selected period. In this case, it should display all the values from the above table, because all the campaigns are between Apr-Jun. but in my case, last two rows are not displaying since the start date is in March but these campaigns are also active during the period of Apr-22 to Jun-22. is there a way we can create some measure to show the campaigns, even if start date before selected slicer date range but end date falls between the selected date range?

Upvotes: 1

Views: 2050

Answers (1)

Mik
Mik

Reputation: 2103

You can try smth like this. The measure should return 1 for “active” if a company name is in scope and total number of active in total. But it would be better to create a DateTable as Ozan Sen advise. Otherwise you can get a wrong output - a slicer will return max and min dates that you have in table not as slicer shows. For instance your slicer shows you the first date as 01 Jan, but in your facts the minimum is 3 Jan, so, this you will get as minDate. The problem can come if the endDate is 02 Jan, the measure will not count it, because the minDate=03 Jan. With a Date table you'll avoid this kind of problem. I didn't check the measure, so I'm not 100% sure it works.

VAR minDate =Min('table'[startDate])
VAR maxDate =Max('table'[startDate])

VAR noOfCompNames = 
    CALCULATE(
        CountRows(table)
        ,OR(
            AND('table'[startDate]>=minDate,'table'[startDate]<= maxDate) 
            ,AND('table'[endDate]<=minDate,'table'[endDate]>=maxDate
                 )
         )    
RETURN
    noOfCompNames

Upvotes: 0

Related Questions