Forum Discussion

JonGunnar's avatar
JonGunnar
Frequent Visitor
4 years ago
Solved

Total active contracts on two selected dates

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))
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

1 Reply

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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.