Help With Dax

[Anonymous]

Hi Power Users,

I'm learning Power BI and trying to implement the below.

I have a table that contains ID,Start date and end date column as below.

ID	Start date	End date
1	12/31/2019	6/1/2020
2	12/1/2019	4/20/2020
3	6/10/2019	12/31/2019
4	1/2/2020	2/22/2020
5	1/3/2020	3/18/2020
6	1/4/2020	3/19/2020
7	7/10/2019	5/20/2020
10	12/31/2019	6/21/2020
11	4/2/2020	3/22/2020

And i have calender date table and have relationship between these 2 table. Start date to date(Active relationship) and end date to date(inactive relationship). 

I have slicer on calender date.

If the user select the a date range from 12/31/2019 to 05/31/2020. The table should caluclate number of ID's. Start date>=12/31/2019 and end date <=05/31/2020.

I need out like below.

Output
ID	Start date	End date
1	12/31/2019	6/1/2020
4	1/2/2020	2/22/2020
5	1/3/2020	3/18/2020
6	1/4/2020	3/19/2020
11	4/2/2020	3/22/2020

Count Of ID: 5

Please  help.

Thanks,

[amitchandak · Accepted solution]

@Anonymous ,

Try

Measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[ID]), filter(all(Table), Table[Start date]>=_min && Table[Start date]<=_max ))

or

Measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[ID]), filter((Table), Table[Start date]>=_min && Table[Start date]<=_max ), crossfilter(Table[Start date],'Date'[Date],none)
, crossfilter(Table[End date],'Date'[Date],none))

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use the following measure formula to get the row count in selected ranges:

Measure = 
CALCULATE (
    COUNT ( Test[ID] ),
    FILTER (
        ALLSELECTED ( Test ),
        COUNTROWS (
            INTERSECT (
                CALENDAR ( Test[Start date], Test[End date] ),
                ALLSELECTED ( 'Calendar'[Date] )
            )
        ) > 0
    )
)

Notice: it seems not works on your sample data due to some records has the wrong date range. (start date over end date)
Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous ,

Try

Measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[ID]), filter(all(Table), Table[Start date]>=_min && Table[Start date]<=_max ))

or

Measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(count(Table[ID]), filter((Table), Table[Start date]>=_min && Table[Start date]<=_max ), crossfilter(Table[Start date],'Date'[Date],none)
, crossfilter(Table[End date],'Date'[Date],none))

[Tahreem24]

Just create measure like below:

Count of ID= Count(Table[IDcolumn])

And select the date range from slicer and see the result.

If this not helped you so please share the screenshot.