Total active contracts on two selected dates

[JonGunnar]

I would like to get the number of active contracts on two specific selected dates in a date slicer. Based on a suggestion from radacad, Power BI From and To Date Filtering with One Slicer - RADACAD I have created two measures on contract table DatoStart and DatoSlutt (end). The date table is without relationship. KONTRRETUR is start date of contract. KONTROPPSEIINGDATO is end date.

The result is correct as shown below. 

DatoStart = var _rangeStart=FIRSTDATE('sheet1 (2)'[DATO]) return

IF(SELECTEDVALUE('Spørring2'[KONTRRETUR])<=_rangeStart && SELECTEDVALUE('Spørring2'[KONTROPPSEIINGDATO])>_rangeStart ,1,0)

DatoSlutt = var _rangeSlutt=LASTDATE('sheet1 (2)'[DATO]) return

IF(SELECTEDVALUE('Spørring2'[KONTRRETUR])<=_rangeSlutt && SELECTEDVALUE('Spørring2'[KONTROPPSEIINGDATO])>_rangeSlutt ,1,0)

To get the total numbers for first date the following create the following measure:

CALCULATE(COUNTROWS('Spørring2'),FILTER('Spørring2','Spørring2'[DatoStart] = 1))

[v-xiaotang · Accepted solution]

Hi @JonGunnar 

Thanks for reaching out to us.

>>to count total active contracts on two selected dates

Please try this, create the 3 measures below,

selectedStart=minx(allselected('Date'),[date])

selectedEnd=maxx(allselected('Date'),[date])

count= calculate(countrows('Fact'),filter(all('Fact'),'Fact'[start date]>=[selectedStart] && 'Fact'[end date]<=[selectedEnd]))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.